sql server常用命令.docx
《sql server常用命令.docx》由会员分享，可在线阅读，更多相关《sql server常用命令.docx（16页珍藏版）》请在冰点文库上搜索。
sqlserver常用命令
系统视图，系统表，系统存储过程的使用
获取数据库中用户表信息
1、获取特定库中所有用户表信息
select*fromsys.tables
select*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_id
fromsys.columnsaleftjoinsys.typesb
ona.user_type_id=b.user_type_id
whereobject_id=object_id（'表名'）
syscolumns与sys.columns表用法类似。
获取索引或主键信息
1、获取对象及对应的索引的信息
select'对象名'=A.name,
'对象类型'=a.type,
'索引名'=B.name,
'索引类型'=caseb.typewhen1then'聚集索引'
when2then'非聚集索引'
when3then'xml索引'
else'空间索引'end,
'主键否'=casewhenb.is_primary_key=1then'主键'else''end
FROMsys.objectsAJOINsys.indexesBONA.object_id=B.object_idWHEREA.type='U'ANDB.nameISNOTNULLorderbya.name
2、获取表的主键及对应的字段
（1）select'表名'=d.name,'主键名'=a.name,'字段名'=c.namefromsys.indexesajoinsys.index_columnsb
ona.object_id=b.object_idanda.index_id=b.index_idjoinsys.columnscona.object_id=c.object_idand
c.column_id=b.column_id
joinsys.objectsdond.object_id=c.object_id
wherea.is_primary_key=1
（2）SELECT'表名'=OBJECT_NAME（b.parent_obj）,
'主键名'=c.name,
'字段名'=a.name
FROMsyscolumnsa,sysobjectsb,sysindexesc,sysindexkeysdWHEREb.xtype='PK'ANDb.parent_obj=a.idANDc.id=a.idANDb.name=c.nameANDd.id=a.id
ANDd.indid=c.indidANDa.colid=d.colid
（3）select'所属架构'=s.name,
'表名'=t.name,
'主键名'=k.name,
'列名'=c.name,
'键列序数'=ic.key_ordinal
fromsys.key_constraintsask
joinsys.tablesast
ont.object_id=k.parent_object_id
joinsys.schemasass
ons.schema_id=t.schema_id
joinsys.index_columnsasic
onic.object_id=t.object_id
andic.index_id=k.unique_index_id
joinsys.columnsasc
onc.object_id=t.object_id
andc.column_id=ic.column_idwherek.type='pk';
（4）使用系统存储过程获取指定表的主键信息
EXECsp_pkeys'表名'--表名只能是当前数据库下的单独表名不能带上架构名
3、查询哪些表创建了主键
select'表名'=a.namefrom
（selectname,object_idfromsys.objectswheretype='u'）aleftjoin
sys.indexesb
ona.object_id=b.object_idandb.is_primary_key=1
whereb.nameisnotnull
注：
查询哪些表没有创建主键，将where条件改成isnull即可。
查找视图信息
1、查看视图属性信息
execsp_help'视图名'
2、查看创建视图脚本
execsp_helptext'视图名'
3、查看当前数据库所有视图基本信息
select*fromsys.views
select*fromsys.objectswheretype='V'
select*fromINFORMATION_SCHEMA.VIEWS
4、查看视图对应的字段及字段属性
select'视图名'=a.name,
'列名'=b.name,
'字段类型'=TYPE_NAME（b.system_type_id）,
'字段长度'=b.max_length
fromsys.viewsajoinsys.columnsb
ona.object_id=b.object_idorderbya.name
5、获取视图中的对象信息
execsp_depends'视图名'
查看存储过程信息
1、基本信息
select*fromsys.procedures
select*fromsys.objectswheretype='P'
2、查看存储过程创建文本
sp_helptext存储过程名称
selecttextfromsyscommentswhereid=object_id（存储过程名称）
3、查看存储过程的参数信息
（1）select'参数名称'=name,
'类型'=type_name（xusertype）,
'长度'=length,
'参数顺序'=colid
fromsyscolumns
whereid=object_id（存储过程名称）
（2）select'参数名称'=name,
'类型'=type_name（system_type_id）,
'长度'=max_length,
'参数顺序'=parameter_id
fromsys.parameters
whereobject_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、获取数据库的基本信息
selectnamefromsysdatabasesorderbyname
2、获取某个数据库的文件信息
select*from[数据库名].[架构名].sysfiles
3、获取数据库磁盘使用情况
execsp_spaceused
4、获取数据库中表的空间使用情况
IFOBJECT_ID（'tempdb..#TB_TEMP_SPACE'）ISNOTNULLDROPTABLE#TB_TEMP_SPACE
GO
CREATETABLE#TB_TEMP_SPACE（
NAMEVARCHAR（500）
ROWSINT
RESERVEDVARCHAR（50）
DATAVARCHAR（50）
INDEX_SIZEVARCHAR（50）
UNUSEDVARCHAR（50）
）
GO
SP_MSFOREACHTABLE'INSERTINTO#TB_TEMP_SPACEexecsp_spaceused''?
'''GO
SELECT*
FROM#TB_TEMP_SPACE
ORDERBYREPLACE（DATA,'KB',''）+0DESC
获取触发器的相关信息
1、查看触发器定义及相关属性信息
（1）execsp_help'触发器名'
（2）查看表中指定类型的触发器的属性信息
execsp_helptrigger['表名'][,['触发器类型']]
--参数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'end
fromsys.objectsajoinsys.triggersbona.object_id=b.parent_id注：
查询单个表或视图的触发器信息加上a.object_id=object_id（表名）条件。
5、禁用和启用触发器命令
禁用：
altertable表名disabletrigger触发器名
启用：
altertable表名enabletrigger触发器名
注：
禁用或启用多个触发器，触发器名之间用逗号隔开
禁用或启用表中全部触发器，将触发器名换成ALL。
6、指定第一个或最后一个触发的after触发器。
execsp_settriggerorder'触发器名','执行顺序','触发事件'
查询触发触发器的对应事件
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'--登录名
[,[@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登录名withpassword='密码',default_database=默认数据库
2、为指定登录名为创建指定数据库上的用户
use指定数据库
（1）executesp_grantdbaccess'登录名','用户'
（2）createuser用户名forlogin登录名
3、授予用户拥有表的权限
grant权限on对象to用户
4、添加数据库角色
executesp_addrole'角色名'
createrole角色名authorization拥有新角色的数据库用户或角色
5、添加角色的成员
executesp_addrolemember'角色名','用户名'
6、设置角色拥有对象的权限
grant权限on对象名to角色名
--=================================================================创建用户并分配权限
--新增登录名
createloginadministorwithpassword='123',default_database=Mail--新增用户
useMail
createuseradminsforloginadministor
--为用户分配权限
grantselectonA_Areatoadmins
--取消分配的权限
revokeselectonA_Areatoadmins
--新增角色
createroleins
--为角色分配权限
grantselectonA_MailZTtoinswithgrantoption
--删除角色对表A_MailZT的查询权限
revokeselectona_mailzttoinsCASCADE
--添加角色ins成员admins
execsp_addrolemember'ins','admins'
--删除角色ins成员admins
execsp_droprolemember'ins','admins'
--删除角色
droproleins--必须先删除角色中所有成员
--删除用户
dropuseradmins
--删除登录账户
droploginadministor
--==================================================================查看数据库关于权限的信息
--查询当前数据库角色信息
execsp_helprole角色名
--提供有关每个数据库中的登录及相关用户的信息
execsp_helplogins登录名
--报告有关当前数据库中数据库级主体的信息。
execsp_helpuser当前数据库用户或角色名
--返回有关当前数据库中某个角色的成员的信息
execsp_helprolemember角色名
第6/10页
--返回SQLServer固定服务器角色的列表
execsp_helpsrvrole固定服务器角色名
sql数据库批量分配权限
declare@sqlvarchar（max）=''
select@sql=@sql+'grantinserton'+name+'toadmins'+CHAR（10）fromsysobjectswherenamelike'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表中的项。
有参数时会同时删除对应的物理备份设备的文件。
3、查询数据库引擎中备份设备的信息
select*frommaster..sysdevices
select*fromsys.backup_devices
4、备份数据库
backupdatabasemailtodisk=备份文件
backupdatabase数据库名to备份设备
5、数据恢复
6、数据库快照恢复
----------------------------------创建数据库DemoDB
createdatabaseDemoDB
onprimary
（name='DemoDB_data',filename='d:
\Demodb_log.mdf',size=5MB,maxsize=10M
B）
logon
（name='DemoDB_log',filename='d:
\Demodb_log.ldf',size=2MB,maxsize=10MB）
go
-------------------------------------在DemoDB创建数据表T1和T2
第7/10页
useDemoDB
createtableT1（idint,namechar（8）,addresschar（13））
go
createtableT2（idint,namechar（8）,addresschar（13））
go
---------------------------------------在DemoDB数据库的T1和T2插入数据useDemoDB
InsertintoT1values（1,'jacky','suzhou'）
InsertintoT1values（2,'Hellen','shanghai'）
InsertintoT2values（1,'Tom','beijing'）
InsertintoT2values（2,'Alice','hangzhou'）
Go
--------------为DemoDB数据库创建数据库快照DemoDB_dbsnapshot_200510201600createdatabaseDemoDB_dbsnapshot_200510201600
on
（name='DemoDB_data',filename='d:
\DemoDB_dbsnapshot_201203091700.mdf'）assnapshotofDemoDB
go
----------------------------------------在数据库快照和数据库中查询T1和T2表useDemoDB_dbsnapshot_200510201600
select*fromdbo.T1
select*fromdbo.T2
go
useDemoDB--在数据库中查看表T1和T2
select*fromdbo.T1
select*fromdbo.T2
go
---------------------------------------------在数据库中修改T1和T2useDemoDB
updateT1
setname='Tony'whereid=1--在DemoDB中更新数据
go
deletefromT1whereid=2--在DemoDB中删除数据
go
dropTableT2--删除T2表
go
------------------------------在数据库快照和数据库中查询T1和T2表
useDemoDB_dbsnapshot_200510201600
select*fromT1
select*fromT2
go
useDemoDB
select*fromT1
select*fromT2
第8/10页
------------------使用数据库快照还原在DemoDB数据库的T1表误删除和更新的数据updateDemoDB.dbo.T1
setname=（selectnamefromDemoDB_dbsnapshot_200510201600.dbo.T1whereid=1）whereid=1
go
insertintoDemoDB.dbo.T1
select*fromDemoDB_dbsnapshot_200510201600.dbo.T1whereid=2go
-------------------
升级会员 