SQLSERVER常用命令.docx
《SQLSERVER常用命令.docx》由会员分享，可在线阅读，更多相关《SQLSERVER常用命令.docx（16页珍藏版）》请在冰豆网上搜索。
SQLSERVER常用命令
系统视图，系统表，系统存储过程的使用
获取数据库中用户表信息
wheretype='U'--用户表
1获取特定库中所有用户表信息select*fromsys.tables
select*fromsys.objects
第二条语句中当type='S'时是系统表
2、获取表的字段信息
select*fromsys.columns
select*fromsyscolumns
object_id=object_id（'表名'）id=OBJECT_ID（'表名'）
3、获取当前库中表的字段及类型信息
（1）select
from
ona.
'字段名'=a.name,
'类型名'=b.name,
'字段长度'
'参数顺序’
sys.columnsuser_type_id
object_id
where
syscolumns与sys.columns
=a.max_length
=a.column」daleftjoin
=b.user_type_id
=object_id（'表名'）表用法类似。
sys.types
获取索引或主键信息
1、获取对象及对应的索引的信息select'对象名'=A.name,
'对象类型’=a.type,
WHEREA.type='U'ANDB.nameISNOTNULLorderbya.name
2、获取表的主键及对应的字段
（1）
select
'表名'
=d.name
，‘主键名'
=a.name,'字段名'=c.name
from
sys.
.indexes
ajoin
sys.index_
.columnsb
ona
.object_id=b.
.object_id
anda
.index_id=b.index_id
join
sys.
.columns
cona.
object_id
=c.object_idand
c.column_
id=b.column」d
join
sys.
.objects
dond.
object_id
=c.object_id
wherea.is_primary_key=1
（2）SELECT'表名’=OBJECT_NAME（b.parent_obj）,
'主键名'=c.name,
'字段名'=a.name
FROMsyscolumnsa,sysobjectsb,sysindexesc,sysindexkeysd
WHEREb.xtype=卩K'ANDb.parent_obj=a.idANDc.id=a.id
ANDb.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」d=ic.column」dwherek.type='pk';
（4）使用系统存储过程获取指定表的主键信息
EXECsp_pkeys'表名’--表名只能是当前数据库下的单独表名不能带上架构名
3、查询哪些表创建了主键
select'表名’=a.namefrom
（selectname,object_idfrom
leftjoin
sys.indexesb
sys
.objects
where
type='u'）a
ona.object_id=b.object_id
and
b.is_primary_key
=1
whereb.nameisnotnull
注：
查询哪些表没有创建主键，将
where条件改成
isnull
即可。
查找视图信息
1、查看视图属性信息
execsp_help'视图名'
2、查看创建视图脚本
execsp_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
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（存储过程名称）
exec
sp_columns
表名
select
*from
sys.columnswhere
object_id
=OBJECT_id（表名）
select
*from
sys.syscolumns
whereid=OBJECT_ID（表名）
select
*from
information_schema
.columns
whereTABLE_NAME=表名
查询存储过程或函数的参数的详细信息
select
*from
sys.parameters
where
object_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（'表名'）and
is_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仓U建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角色名
创建用户并分配权限
--新增登录名
createloginadministor
--新增用户
useMail
--为用户分配权限
toadmins
toadmins
grantselectonA_Area
--取消分配的权限
revokeselectonA_Area
--新增角色
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角色名
--返回SQLServer固定服务器角色的列表
?
?
?
?
如何创建windows用户登录？
？
？
？
sp_dropdevice[@logicaIname=]'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
useDemoDB
create
table
T1（id
int
namechar（8）,address
char（13））
go
create
table
T2（id
int
namechar
（8）,
address
char
（13））
go
在DemoDB数据库的T1和T2插入数据
useDemoDB
Insert
into
T1
values
（1,'jacky'
'suzhou'）
Insert
into
T1
values
（2,'Hellen'
'shanghai'）
Insert
into
T2
values
（1,'Tom',
'beijing'）
Insert
into
T2
values
（2,'Alice'
'hangzhou'）
Go
为DemoDB数据库创建数据库快照DemoDB_dbsnapshot_200510201600
createdatabase
DemoDB_dbsnapshot_200510201600on
（name='DemoDB_data',filename='d:
\DemoDB_dbsnapshot_201203091700.mdf'）
assnapshotofDemoDB
在数据库快照和数据库中查询T1和T2表
go
useDemoDB_dbsnapshot_200510201600
select*fromdbo.T1
go
use
DemoDB--
在数据库中查看表
select
*from
dbo.T1
select
*from
dbo.T2
go
use
DemoDB
updateT1
set
name='Tony'
whereid=1
go
delete
fromT1
whereid=2
go
drop
TableT2
--删除T2表
go
select*fromdbo.T2
T1和T2
在数据库中修改T1和T2
--在DemoDB中更新数据
--在DemoDB中删除数据
在数据库快照和数据库中查询T1和T2表
useDemoDB_dbsnapshot_200510201600
select*fromT1
select*fromT2go
useDemoDBselect*fromT1select*fromT2
go
使用数据库快照还原在DemoDB数据库的T1表误删除和更新的数据
updateDemoDB.dbo.T1
setname=（selectnamefromDemoDB_dbsnapshot_200510201600.dbo.T1where
id=1）whereid=1
go
insertintoDemoDB.dbo.T1
select*fromDemoDB_dbsnapshot_200510201600.dbo.T1whereid=2
go
使用数据库快照还原在DemoDB数据库误删除的T2表
useDemoDB
go
select*intoDemoDB.dbo.T2from
go
--复制进剪贴板中的创建T2的语句
DemoDB_dbsnapshot_200510201600.dbo.T2
在数据库快照和数据库中查询T1和T2表
useDemoDB
select*fromT1
select*fromT2
go
useDemoDB_dbsnapshot_200510201600select*fromT1
select*fromT2
go--注：
如果需要周期创建快照，可以创建作业
在DemoDB中更新数据
useDemoDB
updateT1setname='Funny'whereid=1
go
数据库快照和数据库中查询T1和T2表
select
*from
Demodb.dbo.T1
select
*from
DemoDB_dbsnapshot_200510201600
升级会员 