SQL-SERVER-2012-常用命令

1SQL系统视图，系统表，系统存储过程的使用获取数据库中用户表信息1、获取特定库中所有用户表信息select*fromsys.tablesselect*fromsys.objectswheretype='U'--用户表第二条语句中当type='S'时是系统表2、获取表的字段信息select*fromsys.columnswhereobject_id=object_id('表名')select*fromsyscolumnswhereid=OBJECT_ID('表名')3、获取当前库中表的字段及类型信息（1）select'字段名'=a.name,'类型名'=b.name,'字段长度'=a.max_length,'参数顺序'=a.column_idfromsys.columnsaleftjoinsys.typesbona.user_type_id=b.user_type_idwhereobject_id=object_id('表名')syscolumns与sys.columns表用法类似。获取索引或主键信息1、获取对象及对应的索引的信息select'对象名'=A.name,'对象类型'=a.type,2'索引名'=B.name,'索引类型'=caseb.typewhen1then'聚集索引'when2then'非聚集索引'when3then'xml索引'else'空间索引'end,'主键否'=casewhenb.is_primary_key=1then'主键'else''endFROMsys.objectsAJOINsys.indexesBONA.object_id=B.object_idWHEREA.type='U'ANDB.nameISNOTNULLorderbya.name2、获取表的主键及对应的字段（1）select'表名'=d.name,'主键名'=a.name,'字段名'=c.namefromsys.indexesajoinsys.index_columnsbona.object_id=b.object_idanda.index_id=b.index_idjoinsys.columnscona.object_id=c.object_idandc.column_id=b.column_idjoinsys.objectsdond.object_id=c.object_idwherea.is_primary_key=1（2）SELECT'表名'=OBJECT_NAME(b.parent_obj),'主键名'=c.name,'字段名'=a.nameFROMsyscolumnsa,sysobjectsb,sysindexesc,sysindexkeysdWHEREb.xtype='PK'ANDb.parent_obj=a.idANDc.id=a.id3ANDb.name=c.nameANDd.id=a.idANDd.indid=c.indidANDa.colid=d.colid（3）select'所属架构'=s.name,'表名'=t.name,'主键名'=k.name,'列名'=c.name,'键列序数'=ic.key_ordinalfromsys.key_constraintsaskjoinsys.tablesastont.object_id=k.parent_object_idjoinsys.schemasassons.schema_id=t.schema_idjoinsys.index_columnsasiconic.object_id=t.object_idandic.index_id=k.unique_index_idjoinsys.columnsasconc.object_id=t.object_idandc.column_id=ic.column_idwherek.type='pk';（4）使用系统存储过程获取指定表的主键信息EXECsp_pkeys'表名'--表名只能是当前数据库下的单独表名不能带上架构名3、查询哪些表创建了主键select'表名'=a.namefrom4(selectname,object_idfromsys.objectswheretype='u')aleftjoinsys.indexesbona.object_id=b.object_idandb.is_primary_key=1whereb.nameisnotnull注：查询哪些表没有创建主键，将where条件改成isnull即可。查找视图信息1、查看视图属性信息execsp_help'视图名'2、查看创建视图脚本execsp_helptext'视图名'3、查看当前数据库所有视图基本信息select*fromsys.viewsselect*fromsys.objectswheretype='V'select*fromINFORMATION_SCHEMA.VIEWS4、查看视图对应的字段及字段属性select'视图名'=a.name,'列名'=b.name,'字段类型'=TYPE_NAME(b.system_type_id),'字段长度'=b.max_length5fromsys.viewsajoinsys.columnsbona.object_id=b.object_idorderbya.name5、获取视图中的对象信息execsp_depends'视图名'查看存储过程信息1、基本信息select*fromsys.proceduresselect*fromsys.objectswheretype='P'2、查看存储过程创建文本sp_helptext存储过程名称selecttextfromsyscommentswhereid=object_id(存储过程名称)3、查看存储过程的参数信息(1)select'参数名称'=name,'类型'=type_name(xusertype),'长度'=length,'参数顺序'=colidfromsyscolumnswhereid=object_id(存储过程名称)(2)select'参数名称'=name,'类型'=type_name(system_type_id),'长度'=max_length,6'参数顺序'=parameter_idfromsys.parameterswhereobject_id=object_id(存储过程名称)返回当前环境中可查询的指定表或视图的列信息。execsp_columns表名select*fromsys.columnswhereobject_id=OBJECT_id(表名)select*fromsys.syscolumnswhereid=OBJECT_ID(表名)select*frominformation_schema.columnswhereTABLE_NAME=表名查询存储过程或函数的参数的详细信息select*fromsys.parameterswhereobject_id=object_id(函数或存储过程名称)获取所有数据库信息1、获取数据库的基本信息selectnamefromsysdatabasesorderbyname2、获取某个数据库的文件信息select*from[数据库名].[架构名].sysfiles3、获取数据库磁盘使用情况execsp_spaceused4、获取数据库中表的空间使用情况IFOBJECT_ID('tempdb..#TB_TEMP_SPACE')ISNOTNULLDROPTABLE7#TB_TEMP_SPACEGOCREATETABLE#TB_TEMP_SPACE(NAMEVARCHAR(500),ROWSINT,RESERVEDVARCHAR(50),DATAVARCHAR(50),INDEX_SIZEVARCHAR(50),UNUSEDVARCHAR(50))GOSP_MSFOREACHTABLE'INSERTINTO#TB_TEMP_SPACEexecsp_spaceused''?'''GOSELECT*FROM#TB_TEMP_SPACEORDERBYREPLACE(DATA,'KB','')+0DESC获取触发器的相关信息1、查看触发器定义及相关属性信息（1）execsp_help'触发器名'（2）查看表中指定类型的触发器的属性信息execsp_helptrigger['表名'][,['触发器类型']]8--参数2可选，省略参数2时返回该表中所有类型的触发器属性2、获取触发器的创建脚本execsp_helptext'触发器名'3、查看表中禁用的触发器selectnamefromsys.triggerswhereparent_id=object_id('表名')andis_disabled=1注：is_disabled=0时为启用的触发器。4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息select'父类名'=a.name,'对象类型'=a.type,'触发器名'=b.name,'触发器状态'=casewhenb.is_disabled=1then'禁用'else'启用'end,'触发器类型'=casewhenb.is_instead_of_trigger=1then'insteadof'else'after'endfromsys.objectsajoinsys.triggersbona.object_id=b.parent_id注：查询单个表或视图的触发器信息加上a.object_id=object_id(表名)条件。5、禁用和启用触发器命令禁用：altertable表名disabletrigger触发器名启用：altertable表名enabletrigger触发器名注：禁用或启用多个触发器，触发器名之间用逗号隔开禁用或启用表中全部触发器，将触发器名换成ALL。6、指定第一个或最后一个触发的after触发器。execsp_settriggerorder'触发器名','执行顺序','触发事件'9查询触发触发器的对应事件select*fromsys.trigger_eventswhereobject_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创建sqlserver登录名语法：sp_addlogin[@loginame=]'login'--登录名sp_droplogin删除登录帐号存储过程语法：sp_droplogin[@loginame=]'login'--登录名sp_grantdbaccess将数据库用户添加到当前数据库语法：sp_grantdbaccess[@loginame=]'login'--登录名10[,[@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_hel