簡單的3個SQL視圖搞定所有SqlServer數據庫字典
當前位置：點晴教程→知識管理交流
→『 技術文檔交流 』
網上有很多SQL SERVER數據庫字典的SQL語句，七零八落，我在工作整理了一下思路，總結SQL代碼如下。數據庫字典包括表結構(分2K和2005）、索引和主鍵.外鍵.約束.視圖.函數.存儲過程.觸發器.規則?？梢栽谄髽I管理器、查詢分析器中簡單執行，直接了當的查出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.colorderSqlServer2005數據庫字典--表結構.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.keyno3. 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.idWHERE --(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數據庫技術就是一壇陳年老酒，越久越香，學以致用。 該文章在 2011/3/15 12:39:18 編輯過
|
關鍵字查詢
相關文章
正在查詢... 
|
||||||
400 186 1886
