SQL-SERVER-2008-常用命令Word版

1、系統(tǒng)視圖，系統(tǒng)表，系統(tǒng)存儲過程的使用獲取數(shù)據(jù)庫中用戶表信息1、獲取特定庫中所有用戶表信息select * from sys.tablesselect * from sys.objects where type=U -用戶表第二條語句中當(dāng)type=S時是系統(tǒng)表2、獲取表的字段信息select * from sys.columns where object_id=object_id(表名)select * from syscolumns where id=OBJECT_ID(表名 )3、獲取當(dāng)前庫中表的字段及類型信息（1）select 字段名=, 類型名=, 字段長度=a.

2、max_length, 參數(shù)順序=a.column_id from sys.columns a left join sys.types b on a.user_type_id=b.user_type_idwhere object_id=object_id(表名)syscolumns與sys.columns表用法類似。獲取索引或主鍵信息1、 獲取對象及對應(yīng)的索引的信息select 對象名=A.name, 對象類型=a.type, 索引名=B.name, 索引類型=case b.type when 1 then 聚集索引 when 2 then 非聚集索引 when 3 then xml索引 el

3、se 空間索引 end, 主鍵否=case when b.is_primary_key=1 then 主鍵 else end FROM sys.objects A JOIN sys.indexes B ON A.object_id=B.object_id WHERE A.type=U AND B.name IS NOT NULL order by 2、 獲取表的主鍵及對應(yīng)的字段（1）select 表名= ,主鍵名=,字段名= from sys.indexes a join sys.index_columns b on a.object_id=b.

4、object_id and a.index_id=b.index_id join sys.columns c on a.object_id=c.object_id and c.column_id=b.column_id join sys.objects d on d.object_id=c.object_id where a.is_primary_key=1（2）SELECT 表名=OBJECT_NAME(b.parent_obj), 主鍵名=, 字段名= FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys

5、d WHERE b.xtype = PK AND b.parent_obj = a.id AND c.id = a.id AND = AND d.id = a.id AND d.indid = c.indid AND a.colid = d.colid（3）select 所屬架構(gòu)= , 表名=, 主鍵名= , 列名=, 鍵列序數(shù)=ic.key_ordinalfrom sys.key_constraints as k join sys.tables as t on t.object_id = k.parent_objec

6、t_id join sys.schemas as s on s.schema_id = t.schema_id join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id where k.type = pk;（4）使用系統(tǒng)存儲過程獲取指定表的主鍵信息EXEC sp_pkeys 表名 -表名只能是當(dāng)前數(shù)據(jù)庫

7、下的單獨表名不能帶上架構(gòu)名3、 查詢哪些表創(chuàng)建了主鍵select 表名= from (select name,object_id from sys.objects where type=u) a left join sys.indexes b on a.object_id=b.object_id and b.is_primary_key=1where is not null注：查詢哪些表沒有創(chuàng)建主鍵，將where條件改成 is null 即可。查找視圖信息1、 查看視圖屬性信息exec sp_help 視圖名2、 查看創(chuàng)建視圖腳本exec sp_helptext 視圖

8、名3、 查看當(dāng)前數(shù)據(jù)庫所有視圖基本信息select * from sys.viewsselect * from sys.objects where type=Vselect * from INFORMATION_SCHEMA.VIEWS4、 查看視圖對應(yīng)的字段及字段屬性select 視圖名=, 列名=, 字段類型=TYPE_NAME(b.system_type_id), 字段長度=b.max_lengthfrom sys.views a join sys.columns b on a.object_id=b.object_id order by 5、 獲取視

9、圖中的對象信息exec sp_depends 視圖名查看存儲過程信息1、基本信息select * from cedures select * from sys.objects where type=P2、查看存儲過程創(chuàng)建文本sp_helptext 存儲過程名稱select text from syscomments where id=object_id (存儲過程名稱)3、查看存儲過程的參數(shù)信息(1)select 參數(shù)名稱 = name, 類型 = type_name(xusertype), 長度 = length, 參數(shù)順序 = colid from syscolumns wh

10、ere id=object_id(存儲過程名稱)(2)select 參數(shù)名稱 = name, 類型 = type_name(system_type_id), 長度 = max_length, 參數(shù)順序 =parameter_id from sys.parameters where object_id=object_id(存儲過程名稱)返回當(dāng)前環(huán)境中可查詢的指定表或視圖的列信息。exec sp_columns 表名select * from sys.columns where object_id=OBJECT_id(表名)select * from sys.syscolumns where id

11、=OBJECT_ID(表名)select * from information_schema.columns where TABLE_NAME=表名查詢存儲過程或函數(shù)的參數(shù)的詳細信息select * from sys.parameters where object_id=object_id(函數(shù)或存儲過程名稱)獲取所有數(shù)據(jù)庫信息1、獲取數(shù)據(jù)庫的基本信息select name from sysdatabases order by name2、獲取某個數(shù)據(jù)庫的文件信息select * from 數(shù)據(jù)庫名.架構(gòu)名.sysfiles3、獲取數(shù)據(jù)庫磁盤使用情況exec sp_spaceused4、獲取數(shù)

12、據(jù)庫中表的空間使用情況IF OBJECT_ID(tempdb.#TB_TEMP_SPACE) IS NOT NULL DROP TABLE #TB_TEMP_SPACEGOCREATE TABLE #TB_TEMP_SPACE(NAME VARCHAR(500),ROWS INT,RESERVED VARCHAR(50),DATA VARCHAR(50),INDEX_SIZE VARCHAR(50),UNUSED VARCHAR(50)GOSP_MSFOREACHTABLE INSERT INTO #TB_TEMP_SPACE exec sp_spaceused ?GOSELECT *FROM

13、 #TB_TEMP_SPACEORDER BY REPLACE(DATA,KB,)+0 DESC獲取觸發(fā)器的相關(guān)信息1、查看觸發(fā)器定義及相關(guān)屬性信息（1）exec sp_help 觸發(fā)器名 （2）查看表中指定類型的觸發(fā)器的屬性信息exec sp_helptrigger 表名,觸發(fā)器類型 -參數(shù)2可選，省略參數(shù)2時返回該表中所有類型的觸發(fā)器屬性2、獲取觸發(fā)器的創(chuàng)建腳本 exec sp_helptext 觸發(fā)器名3、查看表中禁用的觸發(fā)器select name from sys.triggers where parent_id=object_id(表名) and is_disabled=1注：is_

14、disabled=0時為啟用的觸發(fā)器。4、獲取觸發(fā)器的父類名，觸發(fā)器名，觸發(fā)器狀態(tài)和觸發(fā)器類型信息select 父類名=, 對象類型=a.type, 觸發(fā)器名=, 觸發(fā)器狀態(tài)=case when b.is_disabled=1 then禁用 else 啟用end, 觸發(fā)器類型=case when b.is_instead_of_trigger=1 then instead of else after end from sys.objects a join sys.triggers b on a.object_id=b.parent_id注：查詢單個表或視圖的觸發(fā)器信息加

15、上a.object_id=object_id(表名)條件。5、禁用和啟用觸發(fā)器命令禁用：alter table表名disable trigger觸發(fā)器名啟用：alter table表名enable trigger觸發(fā)器名注：禁用或啟用多個觸發(fā)器，觸發(fā)器名之間用逗號隔開 禁用或啟用表中全部觸發(fā)器，將觸發(fā)器名換成ALL。6、指定第一個或最后一個觸發(fā)的after觸發(fā)器。exec sp_settriggerorder 觸發(fā)器名, 執(zhí)行順序, 觸發(fā)事件查詢觸發(fā)觸發(fā)器的對應(yīng)事件select * from sys.trigger_events where object_id=object_id(觸發(fā)器名)7

16、、重命名觸發(fā)器exec sp_rename 舊名，新名SQL語句創(chuàng)建登錄名,數(shù)據(jù)庫用戶,數(shù)據(jù)庫角色及分配權(quán)限使用到的存儲過程解釋說明：sp_addlogin 新增登錄賬號存儲過程語法：sp_addlogin loginame = login -登錄名 , passwd = password -登錄密碼 , defdb = database -默認數(shù)據(jù)庫 , deflanguage = language -默認語言 , sid = sid -安全標識號 , encryptopt= encryption_option 密碼傳輸方式sp_grantlogin 創(chuàng)建sql server 登錄名語法：s

17、p_addlogin loginame = login -登錄名sp_droplogin 刪除登錄帳號存儲過程語法：sp_droplogin loginame = login -登錄名sp_grantdbaccess 將數(shù)據(jù)庫用戶添加到當(dāng)前數(shù)據(jù)庫語法：sp_grantdbaccess loginame = login -登錄名 , name_in_db = name_in_db OUTPUT -數(shù)據(jù)庫用戶名sp_addrole 創(chuàng)建數(shù)據(jù)庫角色 語法：sp_addrole rolename = role角色名 , ownername = owner -角色所有者sp_addrolemember

18、為角色添加成員語法：sp_addrolemember rolename = role, -角色名 membername = security_account -成員用戶sp_droprolemember 刪除角色成員sp_helprole rolename = role 返回當(dāng)前數(shù)據(jù)庫中有關(guān)角色的信息1、創(chuàng)建登錄名(1)exec sp_addlogin 登錄名,密碼,默認數(shù)據(jù)庫(2)create login 登錄名 with password=密碼,default_database=默認數(shù)據(jù)庫2、為指定登錄名為創(chuàng)建指定數(shù)據(jù)庫上的用戶use 指定數(shù)據(jù)庫 (1)execute sp_grantdb

19、access 登錄名,用戶(2)create user 用戶名 for login 登錄名3、授予用戶擁有表的權(quán)限grant 權(quán)限 on 對象 to 用戶4、添加數(shù)據(jù)庫角色execute sp_addrole 角色名create role 角色名 authorization 擁有新角色的數(shù)據(jù)庫用戶或角色5、添加角色的成員execute sp_addrolemember 角色名,用戶名6、設(shè)置角色擁有對象的權(quán)限grant 權(quán)限 on 對象名 to 角色名-=創(chuàng)建用戶并分配權(quán)限-新增登錄名create login administor with password=123,default_datab

20、ase=Mail-新增用戶use Mailcreate user admins for login administor -為用戶分配權(quán)限grant select on A_Area to admins -取消分配的權(quán)限r(nóng)evoke select on A_Area to admins-新增角色create role ins -為角色分配權(quán)限grant select on A_MailZT to ins with grant option-刪除角色對表A_MailZT的查詢權(quán)限r(nóng)evoke select on a_mailzt to ins CASCADE-添加角色ins成員adminsexe

21、c sp_addrolemember ins,admins-刪除角色ins成員adminsexec sp_droprolemember ins,admins-刪除角色drop role ins -必須先刪除角色中所有成員-刪除用戶drop user admins-刪除登錄賬戶drop login administor-=查看數(shù)據(jù)庫關(guān)于權(quán)限的信息-查詢當(dāng)前數(shù)據(jù)庫角色信息exec sp_helprole 角色名-提供有關(guān)每個數(shù)據(jù)庫中的登錄及相關(guān)用戶的信息exec sp_helplogins 登錄名-報告有關(guān)當(dāng)前數(shù)據(jù)庫中數(shù)據(jù)庫級主體的信息。exec sp_helpuser 當(dāng)前數(shù)據(jù)庫用戶或角色名-返

22、回有關(guān)當(dāng)前數(shù)據(jù)庫中某個角色的成員的信息exec sp_helprolemember 角色名-返回SQL Server 固定服務(wù)器角色的列表exec sp_helpsrvrole 固定服務(wù)器角色名sql數(shù)據(jù)庫批量分配權(quán)限declare sql varchar(max)=select sql=sql+grant insert on + name + to admins +CHAR(10) from sysobjects where name like a_%exec (sql)？如何創(chuàng)建windows用戶登錄？備份和還原數(shù)據(jù)庫1、創(chuàng)建備份設(shè)備sp_addumpdevice devtype = de

23、vice_type -備份設(shè)備類型, logicalname = logical_name -備份設(shè)備邏輯名稱, physicalname = physical_name 物理名稱EXEC sp_addumpdevice disk, mydiskdump, d:dump1.bak;注：添加邏輯名為mydiskdump物理名為dump1.bak 的disk類型的備份設(shè)備2、刪除備份設(shè)備sp_dropdevice logicalname = device -備份設(shè)備邏輯名稱 , delfile = delfile -指定物理備份設(shè)備文件是否應(yīng)刪除exec sp_dropdevice mydiskd

24、ump,delfile;注：參數(shù)delfile不選時只將備份設(shè)備的邏輯名從數(shù)據(jù)庫引擎中刪除，并刪除對應(yīng)master.sysdevices表中的項。有參數(shù)時會同時刪除對應(yīng)的物理備份設(shè)備的文件。3、 查詢數(shù)據(jù)庫引擎中備份設(shè)備的信息select * from master.sysdevicesselect * from sys.backup_devices4、 備份數(shù)據(jù)庫backup database mail to disk=備份文件backup database 數(shù)據(jù)庫名 to 備份設(shè)備5、 數(shù)據(jù)恢復(fù)6、 數(shù)據(jù)庫快照恢復(fù)-創(chuàng)建數(shù)據(jù)庫DemoDBcreate database DemoDBon p

25、rimary(name=DemoDB_data,filename=d:Demodb_log.mdf,size=5MB,maxsize=10MB)log on (name=DemoDB_log,filename=d:Demodb_log.ldf,size=2MB,maxsize=10MB)go-在DemoDB創(chuàng)建數(shù)據(jù)表T1和T2use DemoDBcreate table T1(id int,name char(8),address char(13)gocreate table T2(id int,name char(8),address char(13)go-在DemoDB數(shù)據(jù)庫的T1和T2插

26、入數(shù)據(jù)use DemoDBInsert 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數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫快照DemoDB_dbsnapshot_200510201600create database DemoDB_dbsnapshot_200510201600on(name=DemoDB_data,filename=d:DemoDB_d

27、bsnapshot_201203091700.mdf)as snapshot of DemoDBgo-在數(shù)據(jù)庫快照和數(shù)據(jù)庫中查詢T1和T2表use DemoDB_dbsnapshot_200510201600select * from dbo.T1select * from dbo.T2gouse DemoDB -在數(shù)據(jù)庫中查看表T1和T2select * from dbo.T1select * from dbo.T2go-在數(shù)據(jù)庫中修改T1和T2use DemoDBupdate T1set name=Tony where id=1 -在DemoDB中更新數(shù)據(jù)godelete from T1

28、where id=2 -在DemoDB中刪除數(shù)據(jù)godrop Table T2 -刪除T2表go-在數(shù)據(jù)庫快照和數(shù)據(jù)庫中查詢T1和T2表use DemoDB_dbsnapshot_200510201600select * from T1select * from T2gouse DemoDBselect * from T1select * from T2go-使用數(shù)據(jù)庫快照還原在DemoDB數(shù)據(jù)庫的T1表誤刪除和更新的數(shù)據(jù)update DemoDB.dbo.T1set name=(select name from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=1) where id=1goinsert into DemoDB.dbo.T1select * from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=2go-使用數(shù)據(jù)庫快照還原在DemoDB數(shù)據(jù)庫誤刪除的T2表use DemoDB-復(fù)制進剪貼板中的創(chuàng)建T2的語句go select