《SQL Server 2008 数据库技术应用》课程教学资源（参考资料）SQL Server常用命令

系统视图，系统表，系统存储过程的使用 获取数据库中用户表信息 1、获取特定库中所有用户表信息 select from sys.tables select*from sys.objects where type='U'-用户表 第二条语句中当type='S'时是系统表 2、获取表的字段信息 select from sys.columns where object_id=object_id(')select from syscolumns where id=OBJECT_.ID('表名') 3、获取当前库中表的字段及类型信息 (1)select'字段名'=a.name, 类型名'=b.name, 字段长度'=a.max_length, 参数顺序'=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表名') syscolumns与sys.columns表用法类似。 获取索引或主键信息 1、获取对象及对应的索引的信息 select’对象名'=A.name ’对象类型'=a.type '索引名'=B.name, 索引类型'=case b.type when 1 then'聚集索引

系统视图，系统表，系统存储过程的使用 获取数据库中用户表信息 1、获取特定库中所有用户表信息 select * from sys.tables select * from sys.objects where type='U' --用户表 第二条语句中当 type='S'时是系统表 2、获取表的字段信息 select * from sys.columns where object_id=object_id('表名') select * from syscolumns where id=OBJECT_ID('表名' ) 3、获取当前库中表的字段及类型信息 （1）select '字段名'=a.name, '类型名'=b.name, '字段长度'=a.max_length, '参数顺序'=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('表名') syscolumns 与 sys.columns 表用法类似。 获取索引或主键信息 1、 获取对象及对应的索引的信息 select '对象名'=A.name, '对象类型'=a.type, '索引名'=B.name, '索引类型'=case b.type when 1 then '聚集索引

when2then'非聚集索引 when3then'xml索引 else'空间索引'end, '主键否'=case when b.is_primary_key=1 then'主键'elseend FROM sys.ob jects A JOIN sys.indexes B ON A.object_id=B.object_idWHERE A.type='U'AND B.name IS NOT NULL order by a.name 2、获取表的主键及对应的字段 (1)select’表名'=d.name,'主键名'=a.name,'字段名'=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.ob ject 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'表名'=OBJECT_NAME(b.parent_obj)), ’主键名'=c.name, '字段名'=a.name FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys d WHERE b.xtype ='PK'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'所属架构'=s.name, '表名'=t.name, '主键名'=k.name, '列名'=c.name, 键列序数'=ic.key_ordinal

when 2 then '非聚集索引' when 3 then 'xml 索引' else '空间索引' end, '主键否'=case when b.is_primary_key=1 then '主键' else '' end FROM sys.objects A JOIN sys.indexes B ON A.object_id=B.object_idWHERE A.type='U' AND B.name IS NOT NULL order by a.name 2、 获取表的主键及对应的字段 （1）select '表名'=d.name ,'主键名'=a.name,'字段名'=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 '表名'=OBJECT_NAME(b.parent_obj), '主键名'=c.name, '字段名'=a.name FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys d WHERE b.xtype = 'PK' 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 '所属架构'=s.name , '表名'=t.name, '主键名'=k.name , '列名'=c.name, '键列序数'=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 ='pk'; (4)使用系统存储过程获取指定表的主键信息 EXEC sp_pkeys'表名'-一表名只能是当前数据库下的单独表名不能带上架构名 3、查询哪些表创建了主键 select'表名'=a.name from (select name,object_id from sys.objects where type='u')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即可。 查找视图信息 1、查看视图属性信息 exec sp_help'视图名 2、查看创建视图脚本

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 = 'pk'; （4）使用系统存储过程获取指定表的主键信息 EXEC sp_pkeys '表名' --表名只能是当前数据库下的单独表名不能带上架构名 3、 查询哪些表创建了主键 select '表名'=a.name from (select name,object_id from sys.objects where type='u') 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 即可。 查找视图信息 1、 查看视图属性信息 exec sp_help '视图名' 2、 查看创建视图脚本

exec sp_helptext'视图名' 3、查看当前数据库所有视图基本信息 select from sys.views select from sys.objects where type='V' select from INFORMATION_SCHEMA.VIEWS 4、查看视图对应的字段及字段属性 select'视图名'=a.name, '列名'=b.name, '字段类型'=TYPE_NAME(b.system_type_.id), ’字段长度'=b.max_length from sys.views a join sys.columns b on a.object_id=b.object_id order by a.name 5、获取视图中的对象信息 exec sp_.depends'视图名' 查看存储过程信息 1、基本信息 select from sys.procedures select from sys.objects where type='p' 2、查看存储过程创建文本 sp_helptext存储过程名称 select text from syscomments where id=object_id(存储过程名称) 3、查看存储过程的参数信息 (l)select’参数名称'=name, 类型'=type_name(xusertype)

exec sp_helptext '视图名' 3、 查看当前数据库所有视图基本信息 select * from sys.views select * from sys.objects where type='V' select * from INFORMATION_SCHEMA.VIEWS 4、 查看视图对应的字段及字段属性 select '视图名'=a.name, '列名'=b.name, '字段类型'=TYPE_NAME(b.system_type_id), '字段长度'=b.max_length from sys.views a join sys.columns b on a.object_id=b.object_id order by a.name 5、 获取视图中的对象信息 exec sp_depends '视图名' 查看存储过程信息 1、基本信息 select * from sys.procedures select * from sys.objects where type='P' 2、查看存储过程创建文本 sp_helptext 存储过程名称 select text 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(存储过程名称) 返回当前环境中可查询的指定表或视图的列信息。 exec sp_columns表名 select from sys.columns where object_id=0BJECT_id() select from sys.syscolumns where id=0BJECT_ID( select from information_schema.columns where TABLE_NAME= 查询存储过程或函数的参数的详细信息 获取所有数据库信息 1、获取数据库的基本信息 select name from sysdatabases order by name 2、获取某个数据库的文件信息 select*from[数据库名].[架构名].sysfiles 3、获取数据库磁盘使用情况

'长度' = 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(存储过程名称) 返回当前环境中可查询的指定表或视图的列信息。 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('tempdb..#TB_TEMP_SPACE')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 SPACHABLE INSERT INTO BTEMP SPACE exec spspaceused SELECT FROM #TB_TEMP_SPACE ORDER BY REPLACE (DATA,'KB','')+0 DESC 获取触发器的相关信息 1、查看触发器定义及相关属性信息 (1)exec sp_.help'触发器名' (2)查看表中指定类型的触发器的属性信息 exec sp_.helptrigger['表名'][，['触发器类型']

exec sp_spaceused 4、获取数据库中表的空间使用情况 IF OBJECT_ID('tempdb..#TB_TEMP_SPACE') 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 'INSERT INTO #TB_TEMP_SPACE exec sp_spaceused ''?''' GO SELECT * FROM #TB_TEMP_SPACE ORDER BY REPLACE(DATA,'KB','')+0 DESC 获取触发器的相关信息 1、查看触发器定义及相关属性信息 （1）exec sp_help '触发器名' （2）查看表中指定类型的触发器的属性信息 exec sp_helptrigger ['表名'][,['触发器类型']]

-一参数2可选，省略参数2时返回该表中所有类型的触发器属性 2、获取触发器的创建脚本 exec sp_helptext'触发器名' 3、查看表中禁用的触发器 select name from sys.triggers where parent_id=object_id(')and is_disabled=1 注：is_disabled=0时为启用的触发器。 4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息 select’父类名'=a.name 对象类型=a.type, 触发器名'=b.name, 触发器状态'=case when b.is_disabled=-1 then'禁用'else'启用'end,'触 发器类型'=case when b.is_instead_of_trigger=-1 then'instead of'else 'after'end from sys.obiects a ioin sys trig ers b on a obiect id=h parent id 注：查询单个表或视图的触发器信息加上a.object_id=object_id(表名)条 件。 5、禁用和启用触发器命令 禁用：alter table表名disable trigger触发器名 启用：alter table表名enable trigger触发器名 注：禁用或启用多个触发器，触发器名之间用逗号隔开 禁用或启用表中全部触发器，将触发器名换成ALL。 6、指定第一个或最后一个触发的after触发器。 exec sp_settriggerorder'触发器名'，'执行顺序'，'触发事件 查询触发触发器的对应事件

--参数 2 可选，省略参数 2 时返回该表中所有类型的触发器属性 2、获取触发器的创建脚本 exec sp_helptext '触发器名' 3、查看表中禁用的触发器 select name from sys.triggers where parent_id=object_id('表名') and is_disabled=1 注：is_disabled=0 时为启用的触发器。 4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息 select '父类名'=a.name, '对象类型'=a.type, '触发器名'=b.name, '触发器状态'=case when b.is_disabled=1 then'禁用' else '启用'end, '触 发器类型'=case when b.is_instead_of_trigger=1 then 'instead of' else 'after' 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 '触发器名', '执行顺序', '触发事件' 查询触发触发器的对应事件

select*from sys.trigger_events where object_id=object_id('触发器名 7、重命名触发器 exec sp_.rename旧名，新名 SQL语句创建登录名，数据库用户，数据库角色及分配权限 使用到的存储过程解释说明： sp_addlogin新增登录账号存储过程 语法：sp_addlogin[loginame=]'login'-登录名 [,[epasswd=]'password']--登录密码 [,[edefdb=]'database']-默认数据库 [,[@deflanguage=]'language']-默认语言 [,[sid=]sid]-安全标识号 [,[encryptopti=]'encryption_,option']-密码传输方式 sp_grantlogin创建sql server登录名 语法：sp_addlogin[eloginame=]'login''-一登录名 sp_droplogin删除登录帐号存储过程 语法：sp_droplogin[eloginame=]'login'一登录名 sp_grantdbaccess将数据库用户添加到当前数据库 语法：sp_grantdbaccess[@loginame=]'login'-登录名 [,[name_in_db=]'name_in_db'[OUTPUT]]一数据库用户名 sp addrole创建数据库角色 语法：sp_addrole[Brolename=]'role'-角色名 [,[Cownername=]'owner']-一角色所有者 sp_addrolemember为角色添加成员 语法：sp_addrolemember[@rolename=]'role',-角色名

select * from sys.trigger_events where object_id=object_id('触发器名 ') 7、重命名触发器 exec sp_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 [erolename =]'role' 返回当前数据库中有关角色的信息 1、创建登录名 (1)exec sp_addlogin'登录名'，'密码，'默认数据库' (2)create login登录名with password-'密码'，default_databases=默认数据 库 2、为指定登录名为创建指定数据库上的用户 use指定数据库 ()execute sp_.grantdbaccess'登录名'，'用户 (②)create user用户名for login登录名 3、授予用户拥有表的权限 grant权限on对象to用户 4、添加数据库角色 execute sp_addrole'角色名 create role角色名authorization拥有新角色的数据库用户或角色 5、添加角色的成员 execute sp_addrolemember'角色名'，'用户名' 6、设置角色拥有对象的权限 grant权限on对象名to角色名 创建用户并分配权限 -一新增登录名

[ @membername = ] 'security_account' --成员用户 sp_droprolemember 删除角色成员 sp_helprole [ [ @rolename = ] 'role' ] 返回当前数据库中有关角色的信息 1、创建登录名 (1)exec sp_addlogin '登录名','密码','默认数据库' (2)create login 登录名 with password='密码',default_database=默认数据 库 2、为指定登录名为创建指定数据库上的用户 use 指定数据库 (1)execute sp_grantdbaccess '登录名','用户' (2)create user 用户名 for login 登录名 3、授予用户拥有表的权限 grant 权限 on 对象 to 用户 4、添加数据库角色 execute sp_addrole '角色名' create role 角色名 authorization 拥有新角色的数据库用户或角色 5、添加角色的成员 execute sp_addrolemember '角色名','用户名' 6、设置角色拥有对象的权限 grant 权限 on 对象名 to 角色名 --================================================================= 创建用户并分配权限 --新增登录名

create login administor with password='123',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 'ins','admins' -删除角色ins成员admins exec sp_droprolemember 'ins','admins' 一删除角色 drop role ins-必须先删除角色中所有成员 一删除用户 drop user admins -一删除登录账户 drop login administor

create login administor with password='123',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 'ins','admins' --删除角色 ins 成员 admins exec sp_droprolemember 'ins','admins' --删除角色 drop role ins --必须先删除角色中所有成员 --删除用户 drop user admins --删除登录账户 drop login administor