SQLSERVER常用命令Word格式.docx

SQLSERVER常用命令Word格式.docx

《SQLSERVER常用命令Word格式.docx》由会员分享，可在线阅读，更多相关《SQLSERVER常用命令Word格式.docx（16页珍藏版）》请在冰豆网上搜索。

select

=d.name

，‘主键名'

=a.name,'

=c.name

sys.

.indexes

ajoin

sys.index_

.columnsb

ona

.object_id=b.

.object_id

anda

.index_id=b.index_id

join

.columns

cona.

=c.object_idand

c.column_

id=b.column」d

join

.objects

dond.

=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,

歹列名'

键列序数’=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

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、查看当前数据库所有视图基本信息

*from

.views

V'

INFORMATION

SCHEMA.

VIEWS

4、查看视图对应的字段及字段属性

）,

字段类型’=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'

=type_name（system_type_id）,

=max_length,

参数顺序'

=parameter_id

fromsys.parameters

whereobject_id=object_id（存储过程名称）

exec

sp_columns

表名

sys.columnswhere

=OBJECT_id（表名）

sys.syscolumns

whereid=OBJECT_ID（表名）

information_schema

whereTABLE_NAME=表名

查询存储过程或函数的参数的详细信息

sys.parameters

=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）

SP_MSFOREACHTABLE'

INSERTINTO#TB_TEMP_SPACEexecsp_spaceused'

?

SELECT*

FROM#TB_TEMP_SPACE

ORDERBYREPLACE（DATA,'

KB'

，“）+0DESC

获取触发器的相关信息

1、查看触发器定义及相关属性信息

（1）execsp_help'

触发器名’

（2）查看表中指定类型的触发器的属性信息

execsp_helptrigger['

][,['

触发器类型'

]]

--参数2可选，省略参数2时返回该表中所有类型的触发器属性

2、获取触发器的创建脚本

触发器名'

3、查看表中禁用的触发器

selectnamefromsys.triggerswhereparent_id=object_id（'

表名'

）and

is_disabled=1

is_disabled=0时为启用的触发器。

4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息

父类名'

触发器名’=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_droplogin删除登录帐号存储过程

sp_droplogin[@loginame=]'

--

sp_grantdbaccess将数据库用户添加到当前数据库

sp_grantdbaccess[@loginame=]'

[,[@name_in_db=]'

name_in_db'

[OUTPUT]]--sp_addrole创建数据库角色

sp_addrole[@rolename=]'

role'

[,[@ownername=]'

owner'

]--sp_addrolemember为角色添加成员

sp_addrolemember[@rolename=]'

--

[@membername=]'

security_account'

--sp_droprolemember删除角色成员

sp_helprole[[@rolename=]'

]返回当前数据库中有关角色的信息

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

grantselectonA_Area

--取消分配的权限

revokeselectonA_Area

--新增角色

createroleins

--为角色分配权限

grantselectonA_MailZTtoinswithgrantoption

--删除角色对表A_MailZT的查询权限

revokeselectona_mailzttoinsCASCADE

--添加角色ins成员admins

execsp_addrolemember'

ins'

'

admins'

--删除角色ins成员admins

execsp_droprolemember'

--删除角色

droproleins--必须先删除角色中所有成员

--删除用户

dropuseradmins

--删除登录账户

droploginadministor

查看数据库关于权限的信息

--查询当前数据库角色信息

execsp_helprole角色名

--提供有关每个数据库中的登录及相关用户的信息

execsp_helplogins登录名

--报告有关当前数据库中数据库级主体的信息。

execsp_helpuser当前数据库用户或角色名

--返回有关当前数据库中某个角色的成员的信息

execsp_helprolemember角色名

--返回SQLServer固定服务器角色的列表

如何创建windows用户登录？

？

？

sp_dropdevice[@logicaIname=]'

device'

[,[@delfile=]'

delfile'

execsp_dropdevice'

mydiskdump'

参数'

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

DemoDB_log'

\Demodb_log.ldf'

size=2MB,maxsize=10MB

go

在DemoDB创建数据表T1和T2

useDemoDB

create

table

T1（id

int

namechar（8）,address

char（13））

T2（id

namechar

（8）,

address

char

（13））

在DemoDB数据库的T1和T2插入数据

Insert

into

T1

values

（1,'

jacky'

suzhou'

）

（2,'

Hellen'

shanghai'

T2

Tom'

beijing'

Alice'

hangzhou'

Go

为DemoDB数据库创建数据库快照DemoDB_dbsnapshot_200510201600

createdatabase

DemoDB_dbsnapshot_200510201600on

\DemoDB_dbsnapshot_201203091700.mdf'

assnapshotofDemoDB

在数据库快照和数据库中查询T1和T2表

useDemoDB_dbsnapshot_200510201600

select*fromdbo.T1

use

DemoDB--

在数据库中查看表

dbo.T1

dbo.T2

DemoDB

updateT1

set

name='

Tony'

whereid=1

delete

fromT1

whereid=2

drop

TableT2

--删除T2表

select*fromdbo.T2

T1和T2

在数据库中修改T1和T2

--在DemoDB中更新数据

--在DemoDB中删除数据

select*fromT1

select*fromT2go

useDemoDBselect*fromT1select*fromT2

使用数据库快照还原在DemoDB数据库的T1表误删除和更新的数据

updateDemoDB.dbo.T1

setname=（selectnamefromDemoDB_dbsnapshot_200510201600.dbo.T1where

id=1）whereid=1

insertintoDemoDB.dbo.T1

select*fromDemoDB_dbsnapshot_200510201600.dbo.T1whereid=2

使用数据库快照还原在DemoDB数据库误删除的T2表

select*intoDemoDB.dbo.T2from

--复制进剪贴板中的创建T2的语句

DemoDB_dbsnapshot_200510201600.dbo.T2

select*fromT2

useDemoDB_dbsnapshot_200510201600select*fromT1

go--注：

如果需要周期创建快照，可以创建作业

在DemoDB中更新数据

updateT1setname='

Funny'

whereid=1

数据库快照和数据库中查询T1和T2表

Demodb.dbo.T1

DemoDB_dbsnapshot_200510201600