[SQL] 條列 Stored Procedure、Function、View、Trigger @ 黃昏的甘蔗

201101171452[SQL] 條列 Stored Procedure、Function、View、Trigger

本篇內容主要說明，如何快速的查閱資料庫裡定義好的「Stored Procedure」、「Function」、「View」跟「Trigger」，這不是什麼技巧，但在接手別人開發專案的時候，特別的有用，所以做個記錄。（如果是想看條列資料表的欄位，請參考）

利用下面的 T-SQL 就可以一次列出，並且看這項物件建立的 T-SQL，當然你若建立之初有給註解，也會顯示在這邊

SELECT o.name ,o.xtype , c.text FROM SYS.SYSOBJECTS o
inner join SYS.SYSCOMMENTS c on o.id =c.id
WHERE xtype<>'D' -- D 是顯示預設值所以不看
order by xtype

要辨別每一個物件代表的意義，要參考下面 Xtype 的說明

物件類型 XType
AF = 彙總函式 (CLR)            C = CHECK 條件約束
D = 預設值或 DEFAULT 條件約束              F = FOREIGN KEY 條件約束
L = 記錄         FN = 純量函數
FS = 組件 (CLR) 純量函數   FT = 組件 (CLR) 資料表值函式
IF = 內嵌資料表函數   IT = 內部資料表   P = 預存程序
PC = 組件 (CLR) 預存程序 PK = PRIMARY KEY 條件約束 (類型是 K)
RF = 複寫篩選預存程序   S = 系統資料表     SN = 同義字 SQ = 服務佇列
TA = 組件 (CLR) DML 觸發程序     TF = 資料表函數   TR = SQL DML 觸發程序
TT = 資料表類型     U = 使用者資料表
UQ = UNIQUE 條件約束 (類型是 K)   V = 檢視   X = 擴充預存程序

當你安裝完 MS-SQL 之後，系統便會建立許多預存程序(Stored Procedure)與檢視表 (View)，底下簡錄一些平常可能會用到的預存程序

名稱	功能說明 - 最完整的說明還是請參考MSDN
sys.sysfiles	觀看資料庫的檔案（邏輯名稱與檔案位置路徑）
sys.sysfilegroups	觀看資料庫的檔案群組（）
sys.syscolumns	列出資料表與檢視的欄位內容，可以搭配 sys.sysobjects 與 sys.systypes 顯示詳細內容
sys.systypes	條列預設與自訂的資料型別
sys.sysusers	觀看資料庫的所有使用者（可運用內建安全性函數 suser_sname()、USER_NAME()得到連線使用者名稱）
sys.sysservers	取得資料庫伺服器相關資訊（可運用 @@SERVERNAME 伺服器名稱、@@SERVICENAME Instance名稱）
sys.sysconfigures	資料庫組態設定，搭配 sp_configure 使用
sys.sysdatabases	查詢已建立的資料庫，CmptLevel 是顯示相容性層級
sys.sysobjects	顯示資料庫物件（包含條件約束、預設值、記錄、規則和預存程序）
sys.sysprocesses	目前執行之處理序（Process），也可以用預存程序 sp_who 查
sys.messages	MS-SQL 的錯誤訊息代碼，Language_id=1028 為中文 select * from sys.messages where language_id =1028
1

附錄一些常用的查詢

查看檢視表 View 與資料表 Table 關連性
SELECT VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
ORDER BY VIEW_NAME

查看檢視表 View 與資料表 Table 的欄位 Column 關連性
SELECT VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
ORDER BY VIEW_NAME, COLUMN_NAME;

稍微混合一下 -- 條列檢視表與資料表欄位對應關係
SELECT T.VIEW_SCHEMA, T.VIEW_NAME, T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE T
INNER JOIN INFORMATION_SCHEMA.VIEW_COLUMN_USAGE C ON T.TABLE_NAME = C.TABLE_NAME
ORDER BY T.VIEW_NAME

查詢 Table 佔用的磁碟大小 - 使用 sp_spaceused

查詢每個資料表的大小 - 資料來源亞當斯 http://ms-net.blogspot.com/2009/10/sqlhow-to-check-database-reference-size.html

DECLARE @tblname varchar(50)            --資料表名稱(使用者資料表名稱變數)
-- 判斷暫存檔是否存在資料，存在則刪除它
if exists (select * from dbo.sysobjects
    where id = object_id(N'dbo.#tmpStatics')
           and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE #tmpStatics   --暫存資料表

CREATE TABLE #tmpStatics (        --建立暫存資料表，這張表是依 sp_spaceused 產出的內容格式所建立的
name       varchar(50) NULL , --資料表名稱
rows       varchar(50) NULL , --資料表現有的資料列數
reserved   varchar(50) NULL , --資料庫中的物件所配置的空間大小
data       varchar(50) NULL ,   --資料所用的空間大小
index_size varchar(50) NULL , --索引所用的空間大小
unused     varchar(50) NULL ) --保留給資料庫中之物件但尚未使用的空間大小
DECLARE cur_TableStatics CURSOR FORWARD_ONLY FOR –宣告 Cursor
SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name --取得使用者資料表名稱
OPEN cur_TableStatics –- 啟用 Cursor
FETCH NEXT FROM cur_TableStatics –-逐步的抓取從 sysobjects 找到的資料表名稱
INTO @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT #tmpStatics –-將 sp_spaceused 查詢到的值，寫入暫存資料表中
      EXEC sp_spaceused @tblname,@updateusage = N'TRUE' --顯示資料表的相關磁碟空間資訊
    FETCH NEXT FROM cur_TableStatics  --抓下一筆
    INTO @tblname
END
CLOSE cur_TableStatics    --結束釋放 cursor
DEALLOCATE cur_TableStatics
SELECT * FROM #tmpStatics --最後把產出的暫存檔做輸出顯示
ORDER BY Data
DROP TABLE #tmpStatics --刪除暫存資料表