首页 > 代码库 > 系统视图，系统表，系统存储过程的使用
系统视图，系统表，系统存储过程的使用
系统视图，系统表，系统存储过程的使用
获取数据库中用户表信息
1、获取特定库中所有用户表信息
select* fromsys.tables
select* fromsys.objects wheretype=‘U‘ --用户表
第二条语句中当type=‘S‘时是系统表
2、获取表的字段信息
select* from sys.columnswhere object_id=object_id(‘表名‘)
select* from syscolumns where id=OBJECT_ID(‘表名‘)
3、获取当前库中表的字段及类型信息
（1）select‘字段名‘=a.name,
‘类型名‘=b.name,
‘字段长度‘=a.max_length,
‘参数顺序‘=a.column_id
fromsys.columnsa left join sys.typesb
ona.user_type_id=b.user_type_id
whereobject_id=object_id(‘表名‘)
syscolumns与sys.columns表用法类似。
获取索引或主键信息
获取对象及对应的索引的信息
select‘对象名‘=A.name,
‘对象类型‘=a.type,
‘索引名‘=B.name,
‘索引类型‘=caseb.typewhen 1 then ‘聚集索引‘
when2 then‘非聚集索引‘
when3 then‘xml索引‘
else‘空间索引‘end,
‘主键否‘=casewhen b.is_primary_key=1then ‘主键‘
else‘‘ end
FROMsys.objectsA JOIN sys.indexesB ON A.object_id=B.object_id
WHEREA.type=‘U‘AND B.nameIS NOT NULL order bya.name
获取表的主键及对应的字段
（1）select‘表名‘=d.name,‘主键名‘=a.name,‘字段名‘=c.name
fromsys.indexesa join sys.index_columnsb
ona.object_id=b.object_idand a.index_id=b.index_id
joinsys.columnsc on a.object_id=c.object_idand
c.column_id=b.column_id
joinsys.objectsd on d.object_id=c.object_id
wherea.is_primary_key=1
（2）SELECT‘表名‘=OBJECT_NAME(b.parent_obj),
‘主键名‘=c.name,
‘字段名‘=a.name
FROMsyscolumns a,sysobjectsb,sysindexesc,sysindexkeysd
WHERE b.xtype= ‘PK‘ AND b.parent_obj= a.idAND c.id= a.id
ANDb.name=c.nameAND d.id= a.id
ANDd.indid= c.indidAND a.colid= d.colid
（3）select‘所属架构‘=s.name,
‘表名‘=t.name,
‘主键名‘=k.name,
‘列名‘=c.name,
‘键列序数‘=ic.key_ordinal
fromsys.key_constraintsas k
joinsys.tablesas t
ont.object_id= k.parent_object_id
joinsys.schemasas s
ons.schema_id= t.schema_id
joinsys.index_columnsas ic
onic.object_id= t.object_id
andic.index_id= k.unique_index_id
joinsys.columnsas c
onc.object_id= t.object_id
andc.column_id= ic.column_idwhere k.type= ‘pk‘;
（4）使用系统存储过程获取指定表的主键信息
EXECsp_pkeys ‘表名‘ --表名只能是当前数据库下的单独表名不能带上架构名
查询哪些表创建了主键
select‘表名‘=a.namefrom
(selectname,object_idfrom sys.objectswhere type=‘u‘)a
leftjoin
sys.indexes b
ona.object_id=b.object_idand b.is_primary_key=1
whereb.nameis not null
注：查询哪些表没有创建主键，将where条件改成 is null即可。
查找视图信息
查看视图属性信息
execsp_help ‘视图名‘
查看创建视图脚本
execsp_helptext ‘视图名‘
查看当前数据库所有视图基本信息
select* from sys.views
select* from sys.objectswhere type=‘V‘
select* from INFORMATION_SCHEMA.VIEWS
查看视图对应的字段及字段属性
select‘视图名‘=a.name,
‘列名‘=b.name,
‘字段类型‘=TYPE_NAME(b.system_type_id),
‘字段长度‘=b.max_length
fromsys.viewsa join sys.columnsb
ona.object_id=b.object_idorder by a.name
获取视图中的对象信息
execsp_depends ‘视图名‘
查看存储过程信息
1、基本信息
select* from sys.procedures
select* from sys.objectswhere type=‘P‘
2、查看存储过程创建文本
sp_helptext 存储过程名称
selecttext from syscomments where id=object_id (存储过程名称)
3、查看存储过程的参数信息
(1)select‘参数名称‘= name,
‘类型‘= type_name(xusertype),
‘长度‘= length,
‘参数顺序‘= colid
from syscolumns
where id=object_id(存储过程名称)
(2)select ‘参数名称‘= name,
‘类型‘= type_name(system_type_id),
‘长度‘= max_length,
‘参数顺序‘=parameter_id
from sys.parameters
where object_id=object_id(存储过程名称)
返回当前环境中可查询的指定表或视图的列信息。
execsp_columns 表名
select* from sys.columnswhere object_id=OBJECT_id(表名)
select* from sys.syscolumnswhere id=OBJECT_ID(表名)
select* from information_schema.columnswhere TABLE_NAME=表名
查询存储过程或函数的参数的详细信息
select* from sys.parameterswhere object_id=object_id(函数或存储过程名称)
获取所有数据库信息
1、获取数据库的基本信息
selectname from sysdatabases order by name
2、获取某个数据库的文件信息
select* from [数据库名].[架构名].sysfiles
3、获取数据库磁盘使用情况
execsp_spaceused
4、获取数据库中表的空间使用情况
IFOBJECT_ID(‘tempdb..#TB_TEMP_SPACE‘)IS NOT NULL DROP TABLE#TB_TEMP_SPACE
GO
CREATETABLE #TB_TEMP_SPACE(
NAMEVARCHAR(500)
,ROWSINT
,RESERVEDVARCHAR(50)
,DATAVARCHAR(50)
,INDEX_SIZEVARCHAR(50)
,UNUSEDVARCHAR(50)
)
GO
SP_MSFOREACHTABLE‘INSERT INTO #TB_TEMP_SPACE execsp_spaceused ‘‘?‘‘‘
GO
SELECT*
FROM#TB_TEMP_SPACE
ORDERBY REPLACE(DATA,‘KB‘,‘‘)+0DESC
获取触发器的相关信息
1、查看触发器定义及相关属性信息
（1）exec sp_help ‘触发器名‘
（2）查看表中指定类型的触发器的属性信息
execsp_helptrigger [‘表名‘][,[‘触发器类型‘]]
--参数2可选，省略参数2时返回该表中所有类型的触发器属性
2、获取触发器的创建脚本
execsp_helptext ‘触发器名‘
3、查看表中禁用的触发器
selectname from sys.triggerswhere parent_id=object_id(‘表名‘)and is_disabled=1
注：is_disabled=0时为启用的触发器。
4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息
select‘父类名‘=a.name,
‘对象类型‘=a.type,
‘触发器名‘=b.name,
‘触发器状态‘=casewhen b.is_disabled=1then‘禁用‘else ‘启用‘end,
‘触发器类型‘=casewhen b.is_instead_of_trigger=1then ‘instead of‘ else ‘after‘ end
fromsys.objectsa join sys.triggersb on a.object_id=b.parent_id
注：查询单个表或视图的触发器信息加上a.object_id=object_id(表名)条件。
5、禁用和启用触发器命令
禁用：altertable表名disabletrigger触发器名
启用：altertable表名enabletrigger触发器名
注：禁用或启用多个触发器，触发器名之间用逗号隔开
禁用或启用表中全部触发器，将触发器名换成ALL。
6、指定第一个或最后一个触发的after触发器。
execsp_settriggerorder ‘触发器名‘, ‘执行顺序‘, ‘触发事件‘
查询触发触发器的对应事件
select* from sys.trigger_eventswhere object_id=object_id(‘触发器名‘)
7、重命名触发器
execsp_rename 旧名，新名
SQL语句创建登录名,数据库用户,数据库角色及分配权限
使用到的存储过程解释说明：
sp_addlogin新增登录账号存储过程
语法：sp_addlogin [@loginame = ] ‘login‘ --登录名
[ , [ @passwd = ] ‘password‘ ] -–登录密码
[ , [ @defdb = ] ‘database‘ ] --默认数据库
[ , [ @deflanguage = ]‘language‘ ] --默认语言
[ , [ @sid = ] sid ] --安全标识号
[ , [ @encryptopt= ]‘encryption_option‘ ] –密码传输方式
sp_grantlogin创建sql server登录名
语法：sp_addlogin [ @loginame = ] ‘login‘ --登录名
sp_droplogin删除登录帐号存储过程
语法：sp_droplogin [@loginame = ] ‘login‘ --登录名
sp_grantdbaccess 将数据库用户添加到当前数据库
语法：sp_grantdbaccess [@loginame = ] ‘login‘ --登录名
[ , [ @name_in_db = ] ‘name_in_db‘ [ OUTPUT ]] --数据库用户名
sp_addrole 创建数据库角色
语法：sp_addrole [ @rolename = ] ‘role‘ –角色名
[ , [ @ownername = ] ‘owner‘ ] --角色所有者
sp_addrolemember 为角色添加成员
语法：sp_addrolemember [ @rolename = ] ‘role‘, --角色名
[ @membername = ] ‘security_account‘ --成员用户
sp_droprolemember 删除角色成员
sp_helprole[ [ @rolename = ] ‘role‘ ]
返回当前数据库中有关角色的信息
1、创建登录名
(1)execsp_addlogin ‘登录名‘,‘密码‘,‘默认数据库‘
(2)createlogin 登录名with password=‘密码‘,default_database=默认数据库
2、为指定登录名为创建指定数据库上的用户
use指定数据库
(1)executesp_grantdbaccess ‘登录名‘,‘用户‘
(2)createuser 用户名for login 登录名
3、授予用户拥有表的权限
grant权限on 对象to 用户
4、添加数据库角色
executesp_addrole ‘角色名‘
createrole 角色名authorization 拥有新角色的数据库用户或角色
5、添加角色的成员
executesp_addrolemember ‘角色名‘,‘用户名‘
6、设置角色拥有对象的权限
grant权限on 对象名to 角色名
--=================================================================
创建用户并分配权限
--新增登录名
createlogin administor with password=‘123‘,default_database=Mail
--新增用户
useMail
createuser admins for login administor
--为用户分配权限
grantselect on A_Area to admins
--取消分配的权限
revokeselect on A_Area to admins
--新增角色
createrole ins
--为角色分配权限
grantselect on A_MailZT to inswith grant option
--删除角色对表A_MailZT的查询权限
revokeselect on a_mailzt to insCASCADE
--添加角色ins成员admins
execsp_addrolemember ‘ins‘,‘admins‘
--删除角色ins成员admins
execsp_droprolemember ‘ins‘,‘admins‘
--删除角色
droprole ins --必须先删除角色中所有成员
--删除用户
dropuser admins
--删除登录账户
droplogin administor
--==================================================================
查看数据库关于权限的信息
--查询当前数据库角色信息
execsp_helprole 角色名
--提供有关每个数据库中的登录及相关用户的信息
execsp_helplogins 登录名
--报告有关当前数据库中数据库级主体的信息。
execsp_helpuser 当前数据库用户或角色名
--返回有关当前数据库中某个角色的成员的信息
execsp_helprolemember 角色名
--返回SQLServer固定服务器角色的列表
execsp_helpsrvrole 固定服务器角色名
sql数据库批量分配权限
declare@sql varchar(max)=‘‘
select@sql=@sql+‘grant insert on ‘+name + ‘ to admins ‘+CHAR(10)from sysobjects where name like‘a_%‘
exec(@sql)
？？？？如何创建windows用户登录？？？？
备份和还原数据库
1、创建备份设备
sp_addumpdevice [ @devtype = ] ‘device_type‘ --备份设备类型
, [@logicalname = ] ‘logical_name‘ --备份设备逻辑名称
, [@physicalname = ] ‘physical_name‘ –物理名称
EXECsp_addumpdevice ‘disk‘, ‘mydiskdump‘, ‘d:\dump1.bak‘;
注：添加逻辑名为mydiskdump物理名为dump1.bak的disk类型的备份设备
2、删除备份设备
sp_dropdevice [ @logicalname = ] ‘device‘ --备份设备逻辑名称
[ , [ @delfile = ] ‘delfile‘ ] --指定物理备份设备文件是否应删除
execsp_dropdevice ‘mydiskdump‘,‘delfile‘;
注：参数‘delfile‘不选时只将备份设备的逻辑名从数据库引擎中删除，并删除对应master..sysdevices表中的项。有参数时会同时删除对应的物理备份设备的文件。
查询数据库引擎中备份设备的信息
select* from master..sysdevices
select* from sys.backup_devices
备份数据库
backupdatabase mail to disk=备份文件
backupdatabase 数据库名to 备份设备
数据恢复
数据库快照恢复
----------------------------------创建数据库DemoDB
createdatabase DemoDB
onprimary
(name=‘DemoDB_data‘,filename=‘d:\Demodb_log.mdf‘,size=5MB,maxsize=10MB)
logon
(name=‘DemoDB_log‘,filename=‘d:\Demodb_log.ldf‘,size=2MB,maxsize=10MB)
go
-------------------------------------在DemoDB创建数据表T1和T2
useDemoDB
createtable T1(idint,namechar(8),addresschar(13))
go
createtable T2(idint,namechar(8),addresschar(13))
go
---------------------------------------在DemoDB数据库的T1和T2插入数据
useDemoDB
Insertinto T1 values(1,‘jacky‘,‘suzhou‘)
Insertinto T1 values(2,‘Hellen‘,‘shanghai‘)
Insertinto T2 values(1,‘Tom‘,‘beijing‘)
Insertinto T2 values(2,‘Alice‘,‘hangzhou‘)
Go
--------------为DemoDB数据库创建数据库快照DemoDB_dbsnapshot_200510201600
createdatabase DemoDB_dbsnapshot_200510201600
on
(name=‘DemoDB_data‘,filename=‘d:\DemoDB_dbsnapshot_201203091700.mdf‘)
assnapshot of DemoDB
go
----------------------------------------在数据库快照和数据库中查询T1和T2表
useDemoDB_dbsnapshot_200510201600
select* from dbo.T1
select* from dbo.T2
go
useDemoDB --在数据库中查看表T1和T2
select* from dbo.T1
select* from dbo.T2
go
---------------------------------------------在数据库中修改T1和T2
useDemoDB
updateT1
setname=‘Tony‘where id=1 --在DemoDB中更新数据
go
deletefrom T1 where id=2 --在DemoDB中删除数据
go
dropTable T2 --删除T2表
go
------------------------------在数据库快照和数据库中查询T1和T2表
useDemoDB_dbsnapshot_200510201600
select* from T1
select* from T2
go
useDemoDB
select* from T1
select* from T2
go
------------------使用数据库快照还原在DemoDB数据库的T1表误删除和更新的数据
updateDemoDB.dbo.T1
setname=(selectname from DemoDB_dbsnapshot_200510201600.dbo.T1where id=1)where id=1
go
insertinto DemoDB.dbo.T1
select* from DemoDB_dbsnapshot_200510201600.dbo.T1where id=2
go
----------------------------使用数据库快照还原在DemoDB数据库误删除的T2表
useDemoDB
--复制进剪贴板中的创建T2的语句
go
select *intoDemoDB.dbo.T2from DemoDB_dbsnapshot_200510201600.dbo.T2
go
------------------------------------在数据库快照和数据库中查询T1和T2表
useDemoDB
select* from T1
select* from T2
go
useDemoDB_dbsnapshot_200510201600
select* from T1
select* from T2
go
------------------------------------------
--注：如果需要周期创建快照，可以创建作业
------------------------------------------在DemoDB中更新数据
useDemoDB
updateT1 set name=‘Funny‘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中更新数据
useDemoDB
updateT1 set name=‘Bob‘ 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数据库
usemaster
restoreDatabase DemoDB from Database_snapshot=‘DemoDB_dbsnapshot_200510201600‘
-------------------------------------------
select* from DemoDB.dbo.T1
select* from DemoDB_dbsnapshot_200510201600.dbo.T1
-------------------------------------------
usemaster
dropdatabase DemoDB_dbsnapshot_200510201600 --删除数据库快照
dropDatabase DemoDB --删除数据库
转载自：http://wenku.baidu.com/link?url=2TnLqDON6Lv_xY9j800t98axR_wswnGCepl8SPeMaaDtaKSSJKFXaR4Z2M0DS3Fd1udxmKLEkN7zX5kC79tUr1l6BU4p5uho5a3KszdrdbK
系统视图，系统表，系统存储过程的使用