SQLSERVER常用命令Word格式.docx

1、select=d.name，主键名=a. name ,=c. namesys .in dexesa joinsys . index_.columns bon a.object_id =b.object_idand a.in dex_id =b. in dex_idjoi n.columnsc on a.=c. object_id andc. column_id =b. columndjoin.objectsd on d.=c. object_idwhere a. is_primary_key =1（2）SELECT 表名=OBJECT_NAME（ b. parent_obj ）,主键名=c.

2、name ,=a. nameFROM syscolumns a, sysobjects b, sysindexes c, sysindexkeys dWHERE b. xtype =卩K AND b. parent_obj = a. id AND c. id = a. idAND b. name = c. name AND d. id = a. idAND d. indid = c. indid AND a. colid = d. colid（3）select 所属架构=s. name ,=t . name,=k. name ,歹列名键列序数=ic . key_ord in alfrom sy

3、s . key_constraints as kjoin sys . tables as ton t . object_id = k. parent_object_idjoin sys . schemas as son s. schema_id = t . schema_idjoin sys .index_columns as icon ic . object_id = t . object_idand ic .in dex_id = k. uni que_ in dex_idjoin sys . columns as con c. object_id = t . object_idand c

4、. columnd = ic . columnd where k. type = pk;（4）使用系统存储过程获取指定表的主键信息EXEC sp_pkeys 表名-表名只能是当前数据库下的单独表名不能带上架构名3、查询哪些表创建了主键select 表名=a. name from（select name, object_id fromleft joinsys .indexes bsystype =u ） aon a. object_id =b. object_idandb. is_primary_key=1where b. name is not null注：查询哪些表没有创建主键，将where

5、条件改成is n ull即可。查找视图信息1、 查看视图属性信息exec sp_help 视图名2、 查看创建视图脚本exec sp_helptext 3、 查看当前数据库所有视图基本信息* from.viewsVINFORMATIONSCHEMA .VIEWS4、查看视图对应的字段及字段属性）,字段类型=TYPE_NAME（ b. system_type_id=b. max_lengthfrom sys . views a join sys . columns bon a. object_id =b. object_id order by a. name5、获取视图中的对象信息exec sp

6、_depends 查看存储过程信息1、 基本信息select * from sys . proceduresselect * from sys . objects where type =P2、 查看存储过程创建文本sp_helptext 存储过程名称select text from syscomments where id =object_id （存储过程名称 ）3、 查看存储过程的参数信息（1）select 参数名称=name ,类型 =type_name （xusertype ）,长度=length ,参数顺序=colidfrom syscolu mnswhere id =object_

7、id （存储过程名称）（2）select =type_name （ system_type_id ）,=max_length ,参数顺序 =parameter_idfrom sys . parameterswhere object_id =object_id （存储过程名称）execsp_colu mns表名sys . columns where= OBJECT_id （表名）sys . syscolumnswhere id =OBJECT_ID （表名）in formatio n_schemawhere TABLE_NAME =表名查询存储过程或函数的参数的详细信息sys . paramet

8、ers= object_id （函数或存储过程名称返回当前环境中可查询的指定表或视图的列信息。获取所有数据库信息1、 获取数据库的基本信息select name from sysdatabases order by name2、 获取某个数据库的文件信息select * from 数据库名.架构名.sysfiles3、 获取数据库磁盘使用情况exec sp_spaceused4、获取数据库中表的空间使用情况IF OBJECT_ID （ tempdb.#TB_TEMP_SPACE ） IS NOT NULL DROP TABLE#TB_TEMP_SPACEGOCREATE TABLE #TB_T

9、EMP_SPACE （NAME VARCHAR（ 500 ）,ROWS INT,RESERVED VARCHAR（ 50 ）,DATA VARCHAR（ 50 ）,INDEX_SIZE VARCHAR（ 50 ）,UNUSED VARCHAR（ 50）SP_MSFOREACHTABLE INSERT INTO #TB_TEMP_SPACE exec sp_spaceused ?SELECT *FROM #TB_TEMP_SPACEORDER BY REPLACE （ DATA, KB ，“ ）+ 0 DESC获取触发器的相关信息1、 查看触发器定义及相关属性信息（1）exec sp_help

10、触发器名（2 ）查看表中指定类型的触发器的属性信息exec sp_helptrigger , 触发器类型-参数2可选，省略参数 2时返回该表中所有类型的触发器属性2、 获取触发器的创建脚本触发器名3、 查看表中禁用的触发器select name from sys . triggers where parent_id =object_id （ 表名） andis_disabled =1is_disabled=0 时为启用的触发器。4、 获取触发器的父类名，触发器名，触发器状态和触发器类型信息父类名触发器名=b. name,触发器状态=case when b. is_disabled =1 the

11、n 禁用else 启用end ,触发器类型=case when b. is_instead_of_trigger = 1 then instead ofelse after endfrom 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 触发器名禁用或启用

12、多个触发器，触发器名之间用逗号隔开禁用或启用表中全部触发器，将触发器名换成 ALL。6、 指定第一个或最后一个触发的 after触发器。exec sp_settriggerorder 触发器名，执行顺序，触发事件查询触发触发器的对应事件select * from sys . trigger_events where object_id =object_id （7、 重命名触发器exec sp_re name 旧名，新名SQL语句创建登录名，数据库用户，数据库角色及分配权限 使用到的存储过程解释说明：sp_addlogin 新增登录账号存储过程语法：sp_addlogi n log in ame

13、 = logi n,passwd = password-,defdb = database-,defla nguage = la nguage,sid = sid -,en cryptopt= e ncrypti on _opti on sp_grantlogin 仓U建 sql server 登录名 - sp_droplogin 删除登录帐号存储过程sp_droplogi n logi name = -sp_gra ntdbaccess 将数据库用户添加到当前数据库sp_gra ntdbaccess logi name = ,n ame_in_db = name_in_db OUTPUT -

14、 sp_addrole 创建数据库角色sp_addrole role name = role,ow nern ame = ow ner- sp_addrolemember 为角色添加成员sp_addrolemember role name = ,-member name = security_acco unt- sp_droprolemember 删除角色成员sp_helprole role name = 返回当前数据库中有关角色的信息1、创建登录名-登录名登录密码默认数据库默认语言安全标识号- 密码传输方式登录名数据库用户名-角色名角色所有者角色名成员用户exec sp_addlogin 登

15、录名,密码默认数据库（2）create log in 登录名 with password =,default_database =默认数据库2、 为指定登录名为创建指定数据库上的用户use指定数据库（1）execute sp_grantdbaccess 用户（2）create user 用户名 for log in 登录名3、 授予用户拥有表的权限grant 权限 on对象to 用户4、 添加数据库角色execute sp_addrole 角色名create role 角色名authorization 拥有新角色的数据库用户或角色5、 添加角色的成员execute sp_addrolememb

16、er 用户名6、 设置角色拥有对象的权限grant 权限on对象名to角色名创建用户并分配权限-新增登录名create log in admi nistor-新增用户use Mail-为用户分配权限to adm insgrant select on A_Area-取消分配的权限revoke select on A_Area-新增角色create role ins-为角色分配权限grant select on A_MailZT to ins with grant option-删除角色对表A_MailZT的查询权限revoke select on a_mailzt to ins CASCADE-

17、添加角色ins成员adminsexec sp_addrolemember ins , admins-删除角色ins成员adminsexec sp_droprolemember -删除角色drop role ins -必须先删除角色中所有成员-删除用户drop user adm ins-删除登录账户drop log in adm ini stor查看数据库关于权限的信息-查询当前数据库角色信息exec sp_helprole 角色名-提供有关每个数据库中的登录及相关用户的信息exec sp_helplog ins 登录名-报告有关当前数据库中数据库级主体的信息。exec sp_helpuser

18、当前数据库用户或角色名-返回有关当前数据库中某个角色的成员的信息exec sp_helprolemember 角色名-返回SQL Server 固定服务器角色的列表如何创建 wi ndows 用户登录？ ？sp_dropdevice logica In ame = device,delfile = delfileexec sp_dropdevice mydiskdump参数master.sysdevices不选时只将备份设备的逻辑名从数据库引擎中删除，并删除对应 表中的项。有参数时会冋时删除对应的物理备份设备的文件。3、查询数据库引擎中备份设备的信息select * from master .

19、 sysdevicesselect * from sys . backup_devices4、 备份数据库backup database mail to disk =备份文件backup database 数据库名 to备份设备5、 数据恢复6、数据库快照恢复 创建数据库DemoDBcreate database DemoDBon primary（n ame =DemoDB_data , file name =d:Demodb_log.mdf , size =5MB, maxsize =10 MB）log onDemoDB_logDemodb_log.ldf , size =2 MB, max

20、size =10 MBgo在DemoDB创建数据表T1和T2use DemoDBcreatetableT1 （ idint,name char （ 8）, addresschar （ 13 ）T2（ id,name char（8）,addresschar（13） 在DemoDB数据库的T1和T2插入数据In sertintoT1values（1, jackysuzhou ）（2, HellenshanghaiT2Tom,beiji ngAlicehangzhouGo为 DemoDB 数据库创建数据库快照 DemoDB_dbs napshot_200510201600create databas

21、eDemoDB_dbs napshot_200510201600 onDemoDB_dbs napshot_201203091700.mdfas sn apshot of DemoDB在数据库快照和数据库中查询 T1和T2表use DemoDB_dbs napshot_200510201600select * from dbo . T1useDemoDB -在数据库中查看表dbo . T1dbo . T2DemoDBupdate T1setn ame =T onywhere id = 1deletefrom T1where id =2dropTable T2-删除T2表select * fro

22、m dbo . T2T1 和 T2在数据库中修改T1和T2-在DemoDB中更新数据-在DemoDB中删除数据select * from T1select * from T2 gouse DemoDB select * from T1 select * from T2 使用数据库快照还原在 DemoDB数据库的T1表误删除和更新的数据update DemoDB. dbo . T1set name =（ select name from DemoDB_dbsnapshot_200510201600 . dbo . T1 whereid =1） where id =1insert into Dem

23、oDB. dbo . T1select * from DemoDB_dbsnapshot_200510201600 . dbo . T1 where id =2 使用数据库快照还原在 DemoDB数据库误删除的T2表select * into DemoDB. dbo . T2 from-复制进剪贴板中的创建 T2的语句DemoDB_dbsnapshot_200510201600 . dbo . T2select * from T2use DemoDB_dbs napshot_200510201600 select * from T1go -注：如果需要周期创建快照，可以创建作业 在DemoDB中更新数据update T1 set n ame = Fu nny where id = 1 数据库快照和数据库中查询 T1和T2表Demodb . dbo . T1DemoDB_dbs napshot_200510201600