數據庫系統視圖，系統表，系統存儲過程的使用

jopen 11年前發布 | 15K 次閱讀數據庫

系統視圖，系統表，系統存儲過程的使用

獲取數據庫中用戶表信息

1、獲取特定庫中所有用戶表信息

select * from sys.tables

select * from sys.objects where type='U' --用戶表

第二條語句中當type='S'時是系統表

2、獲取表的字段信息

select * from sys.columns where object_id=object_id('表名')

select * from syscolumns where id=OBJECT_ID('表名')

3、獲取當前庫中表的字段及類型信息

（1）select '字段名'=a.name,

'類型名'=b.name,

'字段長度'=a.max_length,

'參數順序'=a.column_id

from sys.columns a left join sys.types b

on a.user_type_id=b.user_type_id

where object_id=object_id('表名')

syscolumns與sys.columns表用法類似。

獲取索引或主鍵信息

獲取對象及對應的索引的信息

select '對象名'=A.name,

      '對象類型'=a.type,

      '索引名'=B.name,

      '索引類型'=case b.type when 1 then '聚集索引'

                 when2 then '非聚集索引'

                 when3 then 'xml索引'

                 else'空間索引' end,

      '主鍵否'=case when b.is_primary_key=1then '主鍵'

                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 a.name
獲取表的主鍵及對應的字段

（1）select '表名'=d.name ,'主鍵名'=a.name,'字段名'=c.name

from sys.indexes a join sys.index_columns b

on a.object_id=b.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),

      '主鍵名'=c.name,

      '字段名'=a.name

FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys d

WHERE b.xtype = 'PK' AND b.parent_obj = a.id AND c.id = a.id

AND b.name =c.name AND d.id = a.id

AND d.indid = c.indid AND a.colid = d.colid

（3）select '所屬架構'=s.name ,

      '表名'=t.name,

      '主鍵名'=k.name ,

      '列名'=c.name,

      '鍵列序數'=ic.key_ordinal

from sys.key_constraints as k

join sys.tables as t

on t.object_id = k.parent_object_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）使用系統存儲過程獲取指定表的主鍵信息

EXEC sp_pkeys '表名' --表名只能是當前數據庫下的單獨表名不能帶上架構名
查詢哪些表創建了主鍵

select '表名'=a.name 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=1

where b.name is not null

注：查詢哪些表沒有創建主鍵，將where條件改成 is null即可。

查找視圖信息

查看視圖屬性信息

exec sp_help '視圖名'
查看創建視圖腳本

exec sp_helptext '視圖名'
查看當前數據庫所有視圖基本信息

select * from sys.views

select * from sys.objects where type='V'

select * from INFORMATION_SCHEMA.VIEWS
查看視圖對應的字段及字段屬性

select '視圖名'=a.name,

      '列名'=b.name,

      '字段類型'=TYPE_NAME(b.system_type_id),

      '字段長度'=b.max_length

from sys.views a join sys.columns b

on a.object_id=b.object_id order by a.name
獲取視圖中的對象信息

exec sp_depends '視圖名'

查看存儲過程信息

1、基本信息

select * from sys.procedures

select * from sys.objects where type='P'

2、查看存儲過程創建文本

sp_helptext 存儲過程名稱

select text from syscomments where id=object_id (存儲過程名稱)

3、查看存儲過程的參數信息

(1)select '參數名稱' = name,

'類型' = type_name(xusertype),

'長度' = length,

'參數順序' = colid

from syscolumns

where id=object_id(存儲過程名稱)

(2)select '參數名稱' = name,

'類型' = type_name(system_type_id),

'長度' = max_length,

'參數順序' =parameter_id

from sys.parameters

where object_id=object_id(存儲過程名稱)

返回當前環境中可查詢的指定表或視圖的列信息。

exec sp_columns 表名

select * from sys.columns where object_id=OBJECT_id(表名)

select * from sys.syscolumns where id=OBJECT_ID(表名)

select * from information_schema.columns where TABLE_NAME=表名

查詢存儲過程或函數的參數的詳細信息

select * from sys.parameters where object_id=object_id(函數或存儲過程名稱)

獲取所有數據庫信息

1、獲取數據庫的基本信息

select name from sysdatabases order by name

2、獲取某個數據庫的文件信息

select * from [數據庫名].[架構名].sysfiles

3、獲取數據庫磁盤使用情況

exec sp_spaceused

4、獲取數據庫中表的空間使用情況

IF OBJECT_ID('tempdb..#TB_TEMP_SPACE') IS NOT NULL DROP TABLE #TB_TEMP_SPACE

CREATE TABLE #TB_TEMP_SPACE(

NAME VARCHAR(500)

,ROWS INT

,RESERVED VARCHAR(50)

,DATA VARCHAR(50)

,INDEX_SIZE VARCHAR(50)

,UNUSED VARCHAR(50)

)

SP_MSFOREACHTABLE 'INSERT INTO #TB_TEMP_SPACE execsp_spaceused ''?'''

SELECT *

FROM #TB_TEMP_SPACE

ORDER BY REPLACE(DATA,'KB','')+0DESC

獲取觸發器的相關信息

1、查看觸發器定義及相關屬性信息

（1）exec sp_help '觸發器名'

（2）查看表中指定類型的觸發器的屬性信息

exec sp_helptrigger ['表名'][,['觸發器類型']]

--參數2可選，省略參數2時返回該表中所有類型的觸發器屬性

2、獲取觸發器的創建腳本

exec sp_helptext '觸發器名'

3、查看表中禁用的觸發器

select name from sys.triggers where parent_id=object_id('表名') and is_disabled=1

注：is_disabled=0時為啟用的觸發器。

4、獲取觸發器的父類名，觸發器名，觸發器狀態和觸發器類型信息

select '父類名'=a.name,

'對象類型'=a.type,

'觸發器名'=b.name,

'觸發器狀態'=case when b.is_disabled=1then'禁用' else '啟用'end,

'觸發器類型'=case when b.is_instead_of_trigger=1then 'instead of' else 'after' end

from sys.objects a join sys.triggers b on a.object_id=b.parent_id

注：查詢單個表或視圖的觸發器信息加上a.object_id=object_id(表名)條件。

5、禁用和啟用觸發器命令

禁用：alter table表名disable trigger觸發器名

啟用：alter table表名enable trigger觸發器名

注：禁用或啟用多個觸發器，觸發器名之間用逗號隔開

禁用或啟用表中全部觸發器，將觸發器名換成ALL。

6、指定第一個或最后一個觸發的after觸發器。

exec sp_settriggerorder '觸發器名', '執行順序', '觸發事件'

查詢觸發觸發器的對應事件

select * from sys.trigger_events where object_id=object_id('觸發器名')

7、重命名觸發器

exec sp_rename 舊名，新名

SQL語句創建登錄名,數據庫用戶,數據庫角色及分配權限

使用到的存儲過程解釋說明：

sp_addlogin 新增登錄賬號存儲過程

語法：sp_addlogin [@loginame = ] 'login' --登錄名

[ , [ @passwd = ] 'password' ] -–登錄密碼

[ , [ @defdb = ] 'database' ] --默認數據庫

[ , [ @deflanguage = ]'language' ] --默認語言

[ , [ @sid = ] sid ] --安全標識號

[ , [ @encryptopt= ]'encryption_option' ] –密碼傳輸方式

sp_grantlogin創建sql server登錄名

語法：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)exec sp_addlogin '登錄名','密碼','默認數據庫'

(2)create login 登錄名 with password='密碼',default_database=默認數據庫

2、為指定登錄名為創建指定數據庫上的用戶

use 指定數據庫

(1)execute sp_grantdbaccess '登錄名','用戶'

(2)create user 用戶名 for login 登錄名

3、授予用戶擁有表的權限

grant 權限 on 對象 to 用戶

4、添加數據庫角色

execute sp_addrole '角色名'

create role 角色名 authorization 擁有新角色的數據庫用戶或角色

5、添加角色的成員

execute sp_addrolemember '角色名','用戶名'

6、設置角色擁有對象的權限

grant 權限 on 對象名 to 角色名

--=================================================================

創建用戶并分配權限

--新增登錄名

create login administor with password='123',default_database=Mail

--新增用戶

use Mail

create user admins for login administor

--為用戶分配權限

grant select on A_Area to admins

--取消分配的權限

revoke select on A_Area to admins

--新增角色

create role ins

--為角色分配權限

grant select on A_MailZT to ins with grant option

--刪除角色對表A_MailZT的查詢權限

revoke select on a_mailzt to ins CASCADE

--添加角色ins成員admins

exec sp_addrolemember 'ins','admins'

--刪除角色ins成員admins

exec sp_droprolemember 'ins','admins'

--刪除角色

drop role ins --必須先刪除角色中所有成員

--刪除用戶

drop user admins

--刪除登錄賬戶

drop login administor

--==================================================================

查看數據庫關于權限的信息

--查詢當前數據庫角色信息

exec sp_helprole 角色名

--提供有關每個數據庫中的登錄及相關用戶的信息

exec sp_helplogins 登錄名

--報告有關當前數據庫中數據庫級主體的信息。

exec sp_helpuser 當前數據庫用戶或角色名

--返回有關當前數據庫中某個角色的成員的信息

exec sp_helprolemember 角色名

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

exec sp_helpsrvrole 固定服務器角色名

sql數據庫批量分配權限

declare @sql varchar(max)=''

select @sql=@sql+'grant insert on '+ name + ' to admins '+CHAR(10) from sysobjects where name like 'a_%'

exec(@sql)

？？？？如何創建windows用戶登錄？？？？

備份和還原數據庫

1、創建備份設備

sp_addumpdevice [ @devtype = ] 'device_type' --備份設備類型

, [@logicalname = ] 'logical_name' --備份設備邏輯名稱

, [@physicalname = ] 'physical_name' –物理名稱

EXEC sp_addumpdevice 'disk', 'mydiskdump', 'd:\dump1.bak';

注：添加邏輯名為mydiskdump物理名為dump1.bak的disk類型的備份設備

2、刪除備份設備

sp_dropdevice [ @logicalname = ] 'device' --備份設備邏輯名稱

[ , [ @delfile = ] 'delfile' ] --指定物理備份設備文件是否應刪除

exec sp_dropdevice 'mydiskdump','delfile';

注：參數'delfile'不選時只將備份設備的邏輯名從數據庫引擎中刪除，并刪除對應master..sysdevices表中的項。有參數時會同時刪除對應的物理備份設備的文件。

查詢數據庫引擎中備份設備的信息

select * from master..sysdevices

select * from sys.backup_devices
備份數據庫

backup database mail to disk=備份文件

backup database 數據庫名 to 備份設備
數據恢復

數據庫快照恢復

----------------------------------創建數據庫DemoDB

create database DemoDB

on primary

(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)

-------------------------------------在DemoDB創建數據表T1和T2

use DemoDB

create table T1(id int,name char(8),address char(13))

create table T2(id int,name char(8),address char(13))

---------------------------------------在DemoDB數據庫的T1和T2插入數據

use DemoDB

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')

--------------為DemoDB數據庫創建數據庫快照DemoDB_dbsnapshot_200510201600

create database DemoDB_dbsnapshot_200510201600

(name='DemoDB_data',filename='d:\DemoDB_dbsnapshot_201203091700.mdf')

as snapshot of DemoDB

----------------------------------------在數據庫快照和數據庫中查詢T1和T2表

use DemoDB_dbsnapshot_200510201600

select * from dbo.T1

select * from dbo.T2

use DemoDB --在數據庫中查看表T1和T2

select * from dbo.T1

select * from dbo.T2

---------------------------------------------在數據庫中修改T1和T2

use DemoDB

update T1

set name='Tony' where id=1 --在DemoDB中更新數據

delete from T1 where id=2 --在DemoDB中刪除數據

drop Table T2 --刪除T2表

------------------------------在數據庫快照和數據庫中查詢T1和T2表

use DemoDB_dbsnapshot_200510201600

select * from T1

select * from T2

use DemoDB

select * from T1

select * from T2

------------------使用數據庫快照還原在DemoDB數據庫的T1表誤刪除和更新的數據

update DemoDB.dbo.T1

set name=(select name from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=1) where id=1

insert into DemoDB.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=2

----------------------------使用數據庫快照還原在DemoDB數據庫誤刪除的T2表

use DemoDB

--復制進剪貼板中的創建T2的語句

select *into DemoDB.dbo.T2 from DemoDB_dbsnapshot_200510201600.dbo.T2

------------------------------------在數據庫快照和數據庫中查詢T1和T2表

use DemoDB

select * from T1

select * from T2

use DemoDB_dbsnapshot_200510201600

select * from T1

select * from T2

------------------------------------------

--注：如果需要周期創建快照，可以創建作業

------------------------------------------在DemoDB中更新數據

use DemoDB

update T1 set name='Funny' where id=1

-----------------------------------------數據庫快照和數據庫中查詢T1和T2表

select * from Demodb.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T2

----------------------------------------在DemoDB中更新數據

use DemoDB

update T1 set name='Bob' where id=1

----------------------------------數據庫快照和數據庫中查詢T1和T2表

select * from Demodb.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T2

-----------------------------------------------

/*使用數據庫快照還原整個數據庫*/

-------------------------------------------使用數據庫快照恢復DemoDB數據庫

use master

restore Database DemoDB from Database_snapshot='DemoDB_dbsnapshot_200510201600'

-------------------------------------------

select * from DemoDB.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

-------------------------------------------

use master

drop database DemoDB_dbsnapshot_200510201600 --刪除數據庫快照

drop Database DemoDB --刪除數據庫

轉載自：http://wenku.baidu.com/link?url=2TnLqDON6Lv_xY9j800t98axR_wswnGCepl8SPeMaaDtaKSSJKFXaR4Z2M0DS3Fd1udxmKLEkN7zX5kC79tUr1l6BU4p5uho5a3KszdrdbK

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1414592183450.html

數據庫

數據庫系統視圖，系統表，系統存儲過程的使用

相關經驗

相關資訊

相關文檔

目錄