(2007-11-29更新）網上有很多SQL SERVER數據庫字典的SQL語句，七零八落，我在工作整理了一下思路，總結SQL代碼如下。數據庫字典包括表結構(分2K和2005）、索引和主鍵.外鍵.約束.視圖.函數.存儲過程.觸發器.規則。可以在企業管理器、查詢分析器中簡單執行，直接了當的查出SQL2K及SQL2005的所有數據字典，方便文檔的編寫，希望對大家有幫助。

1. SqlServer2000數據庫字典--表結構.sql
SELECT?TOP?100?PERCENT?--a.id,?
??????CASE?WHEN?a.colorder?=?1?THEN?d.name?ELSE?''?END?AS?表名,?
??????CASE?WHEN?a.colorder?=?1?THEN?isnull(f.value,?'')?ELSE?''?END?AS?表說明,?
??????a.colorder?AS?字段序號,?a.name?AS?字段名,?CASE?WHEN?COLUMNPROPERTY(a.id,?
??????a.name,?'IsIdentity')?=?1?THEN?'√'?ELSE?''?END?AS?標識,?
??????CASE?WHEN?EXISTS
??????????(SELECT?1
?????????FROM?dbo.sysindexes?si?INNER?JOIN
???????????????dbo.sysindexkeys?sik?ON?si.id?=?sik.id?AND?si.indid?=?sik.indid?INNER?JOIN
???????????????dbo.syscolumns?sc?ON?sc.id?=?sik.id?AND?sc.colid?=?sik.colid?INNER?JOIN
???????????????dbo.sysobjects?so?ON?so.name?=?si.name?AND?so.xtype?=?'PK'
?????????WHERE?sc.id?=?a.id?AND?sc.colid?=?a.colid)?THEN?'√'?ELSE?''?END?AS?主鍵,?
??????b.name?AS?類型,?a.length?AS?長度,?COLUMNPROPERTY(a.id,?a.name,?'PRECISION')?
??????AS?精度,?ISNULL(COLUMNPROPERTY(a.id,?a.name,?'Scale'),?0)?AS?小數位數,?
??????CASE?WHEN?a.isnullable?=?1?THEN?'√'?ELSE?''?END?AS?允許空,?ISNULL(e.text,?'')?
??????AS?默認值,?ISNULL(g.[value],?'')?AS?字段說明,?d.crdate?AS?創建時間,?
??????CASE?WHEN?a.colorder?=?1?THEN?d.refdate?ELSE?NULL?END?AS?更改時間
FROM?dbo.syscolumns?a?LEFT?OUTER?JOIN
??????dbo.systypes?b?ON?a.xtype?=?b.xusertype?INNER?JOIN
??????dbo.sysobjects?d?ON?a.id?=?d.id?AND?d.xtype?=?'U'?AND?
??????d.status?>=?0?LEFT?OUTER?JOIN
??????dbo.syscomments?e?ON?a.cdefault?=?e.id?LEFT?OUTER?JOIN
??????dbo.sysproperties?g?ON?a.id?=?g.id?AND?a.colid?=?g.smallid?AND?
??????g.name?=?'MS_Description'?LEFT?OUTER?JOIN
??????dbo.sysproperties?f?ON?d.id?=?f.id?AND?f.smallid?=?0?AND?
??????f.name?=?'MS_Description'
ORDER?BY?d.name,?a.colorder
????SqlServer2005數據庫字典--表結構.sql
SELECT?TOP?100?PERCENT?--a.id,?
??????CASE?WHEN?a.colorder?=?1?THEN?d.name?ELSE?''?END?AS?表名,?
??????CASE?WHEN?a.colorder?=?1?THEN?isnull(f.value,?'')?ELSE?''?END?AS?表說明,?
??????a.colorder?AS?字段序號,?a.name?AS?字段名,?CASE?WHEN?COLUMNPROPERTY(a.id,?
??????a.name,?'IsIdentity')?=?1?THEN?'√'?ELSE?''?END?AS?標識,?
??????CASE?WHEN?EXISTS
??????????(SELECT?1
?????????FROM?dbo.sysindexes?si?INNER?JOIN
???????????????dbo.sysindexkeys?sik?ON?si.id?=?sik.id?AND?si.indid?=?sik.indid?INNER?JOIN
???????????????dbo.syscolumns?sc?ON?sc.id?=?sik.id?AND?sc.colid?=?sik.colid?INNER?JOIN
???????????????dbo.sysobjects?so?ON?so.name?=?si.name?AND?so.xtype?=?'PK'
?????????WHERE?sc.id?=?a.id?AND?sc.colid?=?a.colid)?THEN?'√'?ELSE?''?END?AS?主鍵,?
??????b.name?AS?類型,?a.length?AS?長度,?COLUMNPROPERTY(a.id,?a.name,?'PRECISION')?
??????AS?精度,?ISNULL(COLUMNPROPERTY(a.id,?a.name,?'Scale'),?0)?AS?小數位數,?
??????CASE?WHEN?a.isnullable?=?1?THEN?'√'?ELSE?''?END?AS?允許空,?ISNULL(e.text,?'')?
??????AS?默認值,?ISNULL(g.[value],?'')?AS?字段說明,?d.crdate?AS?創建時間,?
??????CASE?WHEN?a.colorder?=?1?THEN?d.refdate?ELSE?NULL?END?AS?更改時間
FROM?dbo.syscolumns?a?LEFT?OUTER?JOIN
??????dbo.systypes?b?ON?a.xtype?=?b.xusertype?INNER?JOIN
??????dbo.sysobjects?d?ON?a.id?=?d.id?AND?d.xtype?=?'U'?AND?
??????d.status?>=?0?LEFT?OUTER?JOIN
??????dbo.syscomments?e?ON?a.cdefault?=?e.id?LEFT?OUTER?JOIN
??????sys.extended_properties?g?ON?a.id?=?g.major_id?AND?a.colid?=?g.minor_id?AND?
??????g.name?=?'MS_Description'?LEFT?OUTER?JOIN
??????sys.extended_properties?f?ON?d.id?=?f.major_id?AND?f.minor_id?=?0?AND?
??????f.name?=?'MS_Description'
ORDER?BY?d.name,?字段序號
2. SqlServer數據庫字典--索引.sql
SELECT?TOP?100?PERCENT?--a.id,?
??????CASE?WHEN?b.keyno?=?1?THEN?c.name?ELSE?''?END?AS?表名,?
??????CASE?WHEN?b.keyno?=?1?THEN?a.name?ELSE?''?END?AS?索引名稱,?d.name?AS?列名,?
??????b.keyno?AS?索引順序,?CASE?indexkey_property(c.id,?b.indid,?b.keyno,?'isdescending')?
??????WHEN?1?THEN?'降序'?WHEN?0?THEN?'升序'?END?AS?排序,?CASE?WHEN?p.id?IS?NULL?
??????THEN?''?ELSE?'√'?END?AS?主鍵,?CASE?INDEXPROPERTY(c.id,?a.name,?'IsClustered')?
??????WHEN?1?THEN?'√'?WHEN?0?THEN?''?END?AS?聚集,?CASE?INDEXPROPERTY(c.id,?
??????a.name,?'IsUnique')?WHEN?1?THEN?'√'?WHEN?0?THEN?''?END?AS?唯一,?
??????CASE?WHEN?e.id?IS?NULL?THEN?''?ELSE?'√'?END?AS?唯一約束,?
??????a.OrigFillFactor?AS?填充因子,?c.crdate?AS?創建時間,?c.refdate?AS?更改時間
FROM?dbo.sysindexes?a?INNER?JOIN
??????dbo.sysindexkeys?b?ON?a.id?=?b.id?AND?a.indid?=?b.indid?INNER?JOIN
??????dbo.syscolumns?d?ON?b.id?=?d.id?AND?b.colid?=?d.colid?INNER?JOIN
??????dbo.sysobjects?c?ON?a.id?=?c.id?AND?c.xtype?=?'U'?LEFT?OUTER?JOIN
??????dbo.sysobjects?e?ON?e.name?=?a.name?AND?e.xtype?=?'UQ'?LEFT?OUTER?JOIN
??????dbo.sysobjects?p?ON?p.name?=?a.name?AND?p.xtype?=?'PK'
WHERE?(OBJECTPROPERTY(a.id,?N'IsUserTable')?=?1)?AND?(OBJECTPROPERTY(a.id,?
??????N'IsMSShipped')?=?0)?AND?(INDEXPROPERTY(a.id,?a.name,?'IsAutoStatistics')?=?0)
ORDER?BY?c.name,?a.name,?b.keyno
3. SqlServer數據庫字典--表.視圖.函數.存儲過程.觸發器.主鍵.外鍵.約束.規則.sql
SELECT?DISTINCT?
??????TOP?100?PERCENT?isnull(p.name,'')?AS?父對象,?o.xtype,?
??????CASE?o.xtype?WHEN?'C'?THEN?'CHECK?約束'?WHEN?'D'?THEN?'默認值或DEFAULT約束'
???????WHEN?'F'?THEN?'FOREIGNKEY約束'?WHEN?'L'?THEN?'日志'?WHEN?'FN'?THEN?'標量函數'
???????WHEN?'IF'?THEN?'內嵌表函數'?WHEN?'P'?THEN?'存儲過程'?WHEN?'PK'?THEN?'PRIMARYKEY約束'
???????WHEN?'RF'?THEN?'復制篩選存儲過程'?WHEN?'S'?THEN?'系統表'?WHEN?'TF'?THEN?'表函數'
???????WHEN?'TR'?THEN?'觸發器'?WHEN?'U'?THEN?'用戶表'?WHEN?'UQ'?THEN?'UNIQUE?約束'
???????WHEN?'V'?THEN?'視圖'?WHEN?'X'?THEN?'擴展存儲過程'?WHEN?'R'?THEN?'規則'?ELSE?NULL
???????END?AS?類型,?o.name?AS?對象名,?o.crdate?AS?創建時間,?o.refdate?AS?更改時間,?
??????c.text?AS?聲明語句,OBJECTPROPERTY(o.id,?N'IsMSShipped')
FROM?dbo.sysobjects?o?Left?JOIN
??????dbo.sysobjects?p?ON?o.parent_obj?=?p.id?LEFT?OUTER?JOIN
??????dbo.syscomments?c?ON?o.id?=?c.id
WHERE?--(o.xtype?IN?('C','D','F','PK','UQ','L','FN','IF','TF','TR','P','R','RF','X','S','U','V'))?AND?
??????(OBJECTPROPERTY(o.id,?N'IsMSShipped')?=?0)?AND?(isnull(p.name,'')?<>?N'dtproperties')
ORDER?BY?o.xtype?DESC

---

數據庫技術就是一壇陳年老酒，越久越香，學以致用。

posted on 2007-11-09 11:13 簡單 閱讀(4669) 評論(32) ?編輯 收藏 網摘 所屬分類: SQLSERVER.Net開發

評論

?221.226.124.* ????????

有幫助！記下了，多謝！ 2007-11-09 11:52 |

? ??????????

整理的好,學習先 2007-11-09 12:08 | ?

? ??????????

用infomation schema系統視圖就可以 2007-11-09 12:10 | ?

? ??????????

不錯。樓主辛苦了、
值得學習、參考。
2007-11-09 12:21 | ?

?203.187.164.* ????????

第三個 有問題：

報錯信息：

Server: Msg 1540, Level 16, State 1, Line 1
Cannot sort a row of size 8110, which is greater than the allowable maximum of 8094. 2007-11-09 12:33 |

?[樓主] ??????????

在企業管理器或查詢分析器中應該都可以成功執行，代碼沒有問題。
5樓的錯誤信息在于字典數據太多，超過8094條記錄的排序限制，可以取消排序代碼再試一下：
SELECT DISTINCT
o.xtype,
CASE o.xtype WHEN 'X' THEN '擴展存儲過程' WHEN 'TR' THEN '觸發器' WHEN 'PK' THEN
'主鍵' WHEN 'F' THEN '外鍵' WHEN 'C' THEN '約束' WHEN 'V' THEN '視圖' WHEN 'FN'
THEN '函數-標量' WHEN 'IF' THEN '函數-內嵌' WHEN 'TF' THEN '函數-表值' ELSE '存儲過程'
END AS 類型, o.name AS 對象名, o.crdate AS 創建時間, o.refdate AS 更改時間,
c.text AS 聲明語句
FROM dbo.sysobjects o LEFT OUTER JOIN
dbo.syscomments c ON o.id = c.id
WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) 2007-11-09 13:26 | ?

? ??????????

不錯 學習了。。。 2007-11-09 14:13 | ?

?220.164.103.* ????????

記下了，謝謝！ 2007-11-09 15:11 |

? ??????????

在2005中第一個個不能成功
對象名 'dbo.sysproperties' 無效。 2007-11-09 15:18 | ?

? ??????????

經在sql server 2005上測試，
只要將一個SQL的最后兩行替換如下，即可正確運行．
dbo.dtproperties g ON a.id = g.id AND a.colid = g.objectid LEFT OUTER JOIN

dbo.dtproperties f ON d.id = f.id AND f.objectid = 0
2007-11-09 15:27 | ?

? ??????????

呵呵，不錯收藏備用 2007-11-09 15:54 | ?

?[樓主] ??????????

感謝大家的捧場，dbo.sysproperties和dbo.dtproperties兩個對象在SQL2005中都不存在，10樓兄臺真測試通過嗎？
SQL2005中的sys.extended_properties對象可以得到表和字段的說明（描述）。

可以用下面的SQL來在SQL2005中查詢表字典，已測試通過：
SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表說明,
a.colorder AS 字段序號, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標識,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主鍵,
b.name AS 類型, a.length AS 長度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小數位數,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空, ISNULL(e.text, '')
AS 默認值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 創建時間,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改時間
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
ORDER BY d.name, a.colorder 2007-11-09 16:03 | ?

? ??????????

好東西

懶人的福音 2007-11-09 17:35 | ?

? ??????????

我記得我寫了一個回復了，怎么找不到了。 2007-11-09 21:37 | ?

? ??????????

有用! 2007-11-10 08:47 | ?

? ??????????

收了，謝謝博主. 2007-11-12 18:19 | ?

?222.68.147.* ????????

精典之作，感激不盡！
2007-11-13 10:25 |

?[樓主] ??????????

很高興能得到大家的支持和肯定。

今天發現并修正了“表結構.sql”的部分錯誤，正文已修改。
1.
dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK'
改為
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'

2.
dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0
改為
dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
f.name = 'MS_Description'

3.
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
改為
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND
g.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND
f.name = 'MS_Description'
2007-11-14 10:51 | ?

?124.243.195.* ????????

請教大家個問題，數據庫字段名和表名全都設計成大寫和全是小寫在執行的時候，性能有差別嗎？因為我們公司有人說吧數據庫字段全設計成大寫會提高執行速度，是這樣的嗎？
2007-11-15 17:49 |

?[樓主] ??????????

答復19樓兄臺的問題，
SQL是否區別大小寫與SQL排序規則有關，可以在數據庫實例、數據庫、用戶對象三個層次上設置SQL排序規則，后者覆蓋前者的設置，SQL排序規則字符串中CI 指定不區分大小寫，CS 指定區分大小寫。所以我們通常的設置是不區分大小寫，不會影響執行速度。 2007-11-29 15:11 | ?

?[樓主] ??????????

今天更新了主貼的內容“3.SqlServer數據庫字典--表.視圖.函數.存儲過程.觸發器.主鍵.外鍵.約束.規則.sql”，包括了規則在內的所有用戶對象，除了索引之外，所有用戶對象皆存在于dbo.sysobjects中。 2007-11-29 15:13 | ?

? ??????????

載走了


2007-12-11 15:29 | ?

? ??????????

mark 2007-12-27 16:07 | ?

?218.66.159.* ????????

好東西，謝謝樓主分享 2008-01-15 08:31 |

?219.238.217.* ????????

好 ，收藏！期待樓主其他文章 2008-03-05 12:57 |

?222.180.192.* ????????

不錯哦 2008-04-03 08:28 |

?61.178.78.* ????????

謝謝樓主，我工作時間用到你的這篇文章了，。 2008-06-17 23:59 |

? ??????????

好東西，收藏了 2008-08-16 13:26 | ?

? ??????????

非常感謝！
不過，應該過濾系統表，只顯示用戶表就足夠了，一般不會用到系統表的吧 2008-09-04 15:56 | ?

?221.220.113.* ????????

強大！學習！ 轉自：

轉載于:https://www.cnblogs.com/qiantuwuliang/archive/2009/06/06/1497372.html

總結

以上是生活随笔為你收集整理的［数据库字典］简单的3个SQL视图搞定所有SqlServer数据库字典的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。