系统视图，系统表，系统存储过程的使用-mysql教程

系统视图，系统表，系统存储过程的使用 获取数据库中用户表信息 1、获取特定库中所有用户表信息 select * from sys.tables select * from sys.objects where type=U --用户表 第二条语句中当type=S时是系统表 2、获取表的字段信息 select * from sys.columns

&＃65279;&＃65279;
系统视图，系统表，系统存储过程的使用
获取数据库中用户表信息
1、获取特定库中所有用户表信息
select * from sys.tables
select * from sys.objects where type=&＃39;U&＃39; --用户表
第二条语句中当type=&＃39;S&＃39;时是系统表
2、获取表的字段信息
select * from sys.columns where object_id=object_id(&＃39;表名&＃39;)
select * from syscolumns where id=OBJECT_ID(&＃39;表名&＃39; )
3、获取当前库中表的字段及类型信息
（1）select &＃39;字段名&＃39;=a.name,
&＃39;类型名&＃39;=b.name,
&＃39;字段长度&＃39;=a.max_length,
&＃39;参数顺序&＃39;=a.column_id
from sys.columns a left join sys.types b
on a.user_type_id=b.user_type_id
where object_id=object_id(&＃39;表名&＃39;)
syscolumns与sys.columns表用法类似。

获取索引或主键信息

获取对象及对应的索引的信息

select &＃39;对象名&＃39;=A.name,

&＃39;对象类型&＃39;=a.type,

&＃39;索引名&＃39;=B.name,

&＃39;索引类型&＃39;=case b.type when 1 then &＃39;聚集索引&＃39;

when2 then &＃39;非聚集索引&＃39;

when3 then &＃39;xml索引&＃39;

else&＃39;空间索引&＃39; end,

&＃39;主键否&＃39;=case when b.is_primary_key=1 then &＃39;主键&＃39;

else&＃39;&＃39; end

FROM sys.objects A JOIN sys.indexes B ON A.object_id=B.object_id

WHERE A.type=&＃39;U&＃39; AND B.name IS NOT NULL order by a.name

获取表的主键及对应的字段

（1）select &＃39;表名&＃39;=d.name ,&＃39;主键名&＃39;=a.name,&＃39;字段名&＃39;=c.name

from sys.indexes a join sys.index_columns b

on a.object_id=b.object_id and a.index_id=b.index_id

join sys.columns c on a.object_id=c.object_id and

c.column_id=b.column_id

join sys.objects d on d.object_id=c.object_id

where a.is_primary_key=1

（2）SELECT &＃39;表名&＃39;=OBJECT_NAME(b.parent_obj),

&＃39;主键名&＃39;=c.name,

&＃39;字段名&＃39;=a.name

FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys d

WHERE b.xtype = &＃39;PK&＃39; AND b.parent_obj = a.id AND c.id = a.id

AND b.name =c.name AND d.id = a.id

AND d.indid = c.indid AND a.colid = d.colid

（3）select &＃39;所属架构&＃39;=s.name ,

&＃39;表名&＃39;=t.name,

&＃39;主键名&＃39;=k.name ,

&＃39;列名&＃39;=c.name,

&＃39;键列序数&＃39;=ic.key_ordinal

from sys.key_constraints as k

join sys.tables as t

on t.object_id = k.parent_object_id

join sys.schemas as s

on s.schema_id = t.schema_id

join sys.index_columns as ic

on ic.object_id = t.object_id

and ic.index_id = k.unique_index_id

join sys.columns as c

on c.object_id = t.object_id

and c.column_id = ic.column_id where k.type = &＃39;pk&＃39;;

（4）使用系统存储过程获取指定表的主键信息

EXEC sp_pkeys &＃39;表名&＃39; --表名只能是当前数据库下的单独表名不能带上架构名

查询哪些表创建了主键

select &＃39;表名&＃39;=a.name from

(select name,object_id from sys.objects where type=&＃39;u&＃39;) a

left join

sys.indexes b

on a.object_id=b.object_id and b.is_primary_key=1

where b.name is not null

注：查询哪些表没有创建主键，将where条件改成 is null 即可。

查找视图信息

查看视图属性信息

exec sp_help &＃39;视图名&＃39;

查看创建视图脚本

exec sp_helptext &＃39;视图名&＃39;

查看当前数据库所有视图基本信息

select * from sys.views

select * from sys.objects where type=&＃39;V&＃39;

select * from INFORMATION_SCHEMA.VIEWS

查看视图对应的字段及字段属性

select &＃39;视图名&＃39;=a.name,

&＃39;列名&＃39;=b.name,

&＃39;字段类型&＃39;=TYPE_NAME(b.system_type_id),

&＃39;字段长度&＃39;=b.max_length

from sys.views a join sys.columns b

on a.object_id=b.object_id order by a.name

获取视图中的对象信息

exec sp_depends &＃39;视图名&＃39;

查看存储过程信息

1、基本信息

select * from sys.procedures

select * from sys.objects where type=&＃39;P&＃39;

2、查看存储过程创建文本

sp_helptext 存储过程名称

select text from syscomments where id=object_id (存储过程名称)

3、查看存储过程的参数信息

(1)select &＃39;参数名称&＃39; = name,

&＃39;类型&＃39; = type_name(xusertype),

&＃39;长度&＃39; = length,

&＃39;参数顺序&＃39; = colid

from syscolumns

where id=object_id(存储过程名称)

(2)select &＃39;参数名称&＃39; = name,

&＃39;类型&＃39; = type_name(system_type_id),

&＃39;长度&＃39; = max_length,

&＃39;参数顺序&＃39; =parameter_id

from sys.parameters

where object_id=object_id(存储过程名称)

返回当前环境中可查询的指定表或视图的列信息。

exec sp_columns 表名

select * from sys.columns where object_id=OBJECT_id(表名)

select * from sys.syscolumns where id=OBJECT_ID(表名)

select * from information_schema.columns where TABLE_NAME=表名

查询存储过程或函数的参数的详细信息

select * from sys.parameters where object_id=object_id(函数或存储过程名称)

获取所有数据库信息

1、获取数据库的基本信息

select name from sysdatabases order by name

2、获取某个数据库的文件信息

select * from [数据库名].[架构名].sysfiles

3、获取数据库磁盘使用情况

exec sp_spaceused

4、获取数据库中表的空间使用情况

IF OBJECT_ID(&＃39;tempdb..#TB_TEMP_SPACE&＃39;) IS NOT NULL DROP TABLE #TB_TEMP_SPACE

GO

CREATE TABLE #TB_TEMP_SPACE(

NAME VARCHAR(500)

,ROWS INT

,RESERVED VARCHAR(50)

,DATA VARCHAR(50)

,INDEX_SIZE VARCHAR(50)

,UNUSED VARCHAR(50)

)

GO

SP_MSFOREACHTABLE &＃39;INSERT INTO #TB_TEMP_SPACE execsp_spaceused &＃39;&＃39;?&＃39;&＃39;&＃39;

GO

SELECT *

FROM #TB_TEMP_SPACE

ORDER BY REPLACE(DATA,&＃39;KB&＃39;,&＃39;&＃39;)+0 DESC

获取触发器的相关信息

1、查看触发器定义及相关属性信息

（1）exec sp_help &＃39;触发器名&＃39;

（2）查看表中指定类型的触发器的属性信息

exec sp_helptrigger [&＃39;表名&＃39;][,[&＃39;触发器类型&＃39;]]

--参数2可选，省略参数2时返回该表中所有类型的触发器属性

2、获取触发器的创建脚本

exec sp_helptext &＃39;触发器名&＃39;

3、查看表中禁用的触发器

select name from sys.triggers where parent_id=object_id(&＃39;表名&＃39;) and is_disabled=1

注：is_disabled=0时为启用的触发器。

4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息

select &＃39;父类名&＃39;=a.name,

&＃39;对象类型&＃39;=a.type,

&＃39;触发器名&＃39;=b.name,

&＃39;触发器状态&＃39;=case when b.is_disabled=1 then&＃39;禁用&＃39; else &＃39;启用&＃39;end,

&＃39;触发器类型&＃39;=case when b.is_instead_of_trigger=1 then &＃39;instead of&＃39; else &＃39;after&＃39; end

from sys.objects a join sys.triggers b on a.object_id=b.parent_id

注：查询单个表或视图的触发器信息加上a.object_id=object_id(表名)条件。

5、禁用和启用触发器命令

禁用：alter table表名disable trigger触发器名

启用：alter table表名enable trigger触发器名

注：禁用或启用多个触发器，触发器名之间用逗号隔开

禁用或启用表中全部触发器，将触发器名换成ALL。

6、指定第一个或最后一个触发的after触发器。

exec sp_settriggerorder &＃39;触发器名&＃39;, &＃39;执行顺序&＃39;, &＃39;触发事件&＃39;

查询触发触发器的对应事件

select * from sys.trigger_events where object_id=object_id(&＃39;触发器名&＃39;)

7、重命名触发器

exec sp_rename 旧名，新名

SQL语句创建登录名,数据库用户,数据库角色及分配权限

使用到的存储过程解释说明：

sp_addlogin 新增登录账号存储过程

语法：sp_addlogin [@loginame = ] &＃39;login&＃39; --登录名

[ , [ @passwd = ] &＃39;password&＃39; ] -–登录密码

[ , [ @defdb = ] &＃39;database&＃39; ] --默认数据库

[ , [ @deflanguage = ]&＃39;language&＃39; ] --默认语言

[ , [ @sid = ] sid ] --安全标识号

[ , [ @encryptopt= ]&＃39;encryption_option&＃39; ] –密码传输方式

sp_grantlogin 创建sql server 登录名

语法：sp_addlogin [ @loginame = ] &＃39;login&＃39; --登录名

sp_droplogin 删除登录帐号存储过程

语法：sp_droplogin [@loginame = ] &＃39;login&＃39; --登录名

sp_grantdbaccess 将数据库用户添加到当前数据库

语法：sp_grantdbaccess [@loginame = ] &＃39;login&＃39; --登录名

[ , [ @name_in_db = ] &＃39;name_in_db&＃39; [ OUTPUT ]] --数据库用户名

sp_addrole 创建数据库角色

语法：sp_addrole [ @rolename = ] &＃39;role&＃39; –角色名

[ , [ @ownername = ] &＃39;owner&＃39; ] --角色所有者

sp_addrolemember 为角色添加成员

语法：sp_addrolemember [ @rolename = ] &＃39;role&＃39;, --角色名

[ @membername = ] &＃39;security_account&＃39; --成员用户

sp_droprolemember 删除角色成员

sp_helprole [ [ @rolename = ] &＃39;role&＃39; ]

返回当前数据库中有关角色的信息

1、创建登录名

(1)exec sp_addlogin &＃39;登录名&＃39;,&＃39;密码&＃39;,&＃39;默认数据库&＃39;

(2)create login 登录名 with password=&＃39;密码&＃39;,default_database=默认数据库

2、为指定登录名为创建指定数据库上的用户

use 指定数据库

(1)execute sp_grantdbaccess &＃39;登录名&＃39;,&＃39;用户&＃39;

(2)create user 用户名 for login 登录名

3、授予用户拥有表的权限

grant 权限 on 对象 to 用户

4、添加数据库角色

execute sp_addrole &＃39;角色名&＃39;

create role 角色名 authorization 拥有新角色的数据库用户或角色

5、添加角色的成员

execute sp_addrolemember &＃39;角色名&＃39;,&＃39;用户名&＃39;

6、设置角色拥有对象的权限

grant 权限 on 对象名 to 角色名

--=================================================================

创建用户并分配权限

--新增登录名

create login administor with password=&＃39;123&＃39;,default_database=Mail

--新增用户

use Mail

create user admins for login administor

--为用户分配权限

grant select on A_Area to admins

--取消分配的权限

revoke select on A_Area to admins

--新增角色

create role ins

--为角色分配权限

grant select on A_MailZT to ins with grant option

--删除角色对表A_MailZT的查询权限

revoke select on a_mailzt to ins CASCADE

--添加角色ins成员admins

exec sp_addrolemember &＃39;ins&＃39;,&＃39;admins&＃39;

--删除角色ins成员admins

exec sp_droprolemember &＃39;ins&＃39;,&＃39;admins&＃39;

--删除角色

drop role ins --必须先删除角色中所有成员

--删除用户

drop user admins

--删除登录账户

drop login administor

--==================================================================

查看数据库关于权限的信息

--查询当前数据库角色信息

exec sp_helprole 角色名

--提供有关每个数据库中的登录及相关用户的信息

exec sp_helplogins 登录名

--报告有关当前数据库中数据库级主体的信息。

exec sp_helpuser 当前数据库用户或角色名

--返回有关当前数据库中某个角色的成员的信息

exec sp_helprolemember 角色名

--返回SQLServer 固定服务器角色的列表

exec sp_helpsrvrole 固定服务器角色名

sql数据库批量分配权限

declare @sql varchar(max)=&＃39;&＃39;

select @sql=@sql+&＃39;grant insert on &＃39;+ name + &＃39; to admins &＃39;+CHAR(10) from sysobjects where name like &＃39;a_%&＃39;

exec (@sql)

？？？？如何创建windows用户登录？？？？

备份和还原数据库

1、创建备份设备

sp_addumpdevice [ @devtype = ] &＃39;device_type&＃39; --备份设备类型

, [@logicalname = ] &＃39;logical_name&＃39; --备份设备逻辑名称

, [@physicalname = ] &＃39;physical_name&＃39; –物理名称

EXEC sp_addumpdevice &＃39;disk&＃39;, &＃39;mydiskdump&＃39;, &＃39;d:\dump1.bak&＃39;;

注：添加逻辑名为mydiskdump物理名为dump1.bak 的disk类型的备份设备

2、删除备份设备

sp_dropdevice [ @logicalname = ] &＃39;device&＃39; --备份设备逻辑名称

[ , [ @delfile = ] &＃39;delfile&＃39; ] --指定物理备份设备文件是否应删除

exec sp_dropdevice &＃39;mydiskdump&＃39;,&＃39;delfile&＃39;;

注：参数&＃39;delfile&＃39;不选时只将备份设备的逻辑名从数据库引擎中删除，并删除对应master..sysdevices表中的项。有参数时会同时删除对应的物理备份设备的文件。

查询数据库引擎中备份设备的信息

select * from master..sysdevices

select * from sys.backup_devices

备份数据库

backup database mail to disk=备份文件

backup database 数据库名 to 备份设备

数据恢复

数据库快照恢复

----------------------------------创建数据库DemoDB

create database DemoDB

on primary

(name=&＃39;DemoDB_data&＃39;,filename=&＃39;d:\Demodb_log.mdf&＃39;,size=5MB,maxsize=10MB)

log on

(name=&＃39;DemoDB_log&＃39;,filename=&＃39;d:\Demodb_log.ldf&＃39;,size=2MB,maxsize=10MB)

go

-------------------------------------在DemoDB创建数据表T1和T2

use DemoDB

create table T1(id int,name char(8),address char(13))

go

create table T2(id int,name char(8),address char(13))

go

---------------------------------------在DemoDB数据库的T1和T2插入数据

use DemoDB

Insert into T1 values(1,&＃39;jacky&＃39;,&＃39;suzhou&＃39;)

Insert into T1 values(2,&＃39;Hellen&＃39;,&＃39;shanghai&＃39;)

Insert into T2 values(1,&＃39;Tom&＃39;,&＃39;beijing&＃39;)

Insert into T2 values(2,&＃39;Alice&＃39;,&＃39;hangzhou&＃39;)

Go

--------------为DemoDB数据库创建数据库快照DemoDB_dbsnapshot_200510201600

create database DemoDB_dbsnapshot_200510201600

on

(name=&＃39;DemoDB_data&＃39;,filename=&＃39;d:\DemoDB_dbsnapshot_201203091700.mdf&＃39;)

as snapshot of DemoDB

go

----------------------------------------在数据库快照和数据库中查询T1和T2表

use DemoDB_dbsnapshot_200510201600

select * from dbo.T1

select * from dbo.T2

go

use DemoDB --在数据库中查看表T1和T2

select * from dbo.T1

select * from dbo.T2

go

---------------------------------------------在数据库中修改T1和T2

use DemoDB

update T1

set name=&＃39;Tony&＃39; where id=1 --在DemoDB中更新数据

go

delete from T1 where id=2 --在DemoDB中删除数据

go

drop Table T2 --删除T2表

go

------------------------------在数据库快照和数据库中查询T1和T2表

use DemoDB_dbsnapshot_200510201600

select * from T1

select * from T2

go

use DemoDB

select * from T1

select * from T2

go

------------------使用数据库快照还原在DemoDB数据库的T1表误删除和更新的数据

update DemoDB.dbo.T1

set name=(select name from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=1) where id=1

go

insert into DemoDB.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=2

go

----------------------------使用数据库快照还原在DemoDB数据库误删除的T2表

use DemoDB

--复制进剪贴板中的创建T2的语句

go

select *into DemoDB.dbo.T2 from DemoDB_dbsnapshot_200510201600.dbo.T2

go

------------------------------------在数据库快照和数据库中查询T1和T2表

use DemoDB

select * from T1

select * from T2

go

use DemoDB_dbsnapshot_200510201600

select * from T1

select * from T2

go

------------------------------------------

--注：如果需要周期创建快照，可以创建作业

------------------------------------------在DemoDB中更新数据

use DemoDB

update T1 set name=&＃39;Funny&＃39; where id=1

go

-----------------------------------------数据库快照和数据库中查询T1和T2表

select * from Demodb.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T2

----------------------------------------在DemoDB中更新数据

use DemoDB

update T1 set name=&＃39;Bob&＃39; where id=1

go

----------------------------------数据库快照和数据库中查询T1和T2表

select * from Demodb.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T2

-----------------------------------------------

/*使用数据库快照还原整个数据库*/

-------------------------------------------使用数据库快照恢复DemoDB数据库

use master

restore Database DemoDB from Database_snapshot=&＃39;DemoDB_dbsnapshot_200510201600&＃39;

-------------------------------------------

select * from DemoDB.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

-------------------------------------------

use master

drop database DemoDB_dbsnapshot_200510201600 --删除数据库快照

drop Database DemoDB --删除数据库

转载自：http://wenku.baidu.com/link?url=2TnLqDON6Lv_xY9j800t98axR_wswnGCepl8SPeMaaDtaKSSJKFXaR4Z2M0DS3Fd1udxmKLEkN7zX5kC79tUr1l6BU4p5uho5a3KszdrdbK