建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
利用三個SQL視圖查出所有SQL Server數(shù)據(jù)庫字典
我們知道，數(shù)據(jù)庫字典包括表結(jié)構(gòu)、索引和主鍵、外鍵、約束、視圖、函數(shù)、存儲過程、觸發(fā)器和規(guī)則。本文我們總結(jié)了三個SQL視圖來查詢數(shù)據(jù)字典的SQL語句示例，利用它可以直接了當?shù)夭槌鯯QL2K及SQL2005的所有數(shù)據(jù)字典，方便文檔的編寫，希望對大家有幫助。
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、網(wǎng)站制作、烏蘭察布網(wǎng)絡(luò)推廣、小程序開發(fā)、烏蘭察布網(wǎng)絡(luò)營銷、烏蘭察布企業(yè)策劃、烏蘭察布品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供烏蘭察布建站搭建服務(wù)，24小時服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com
1. SQL Server數(shù)據(jù)庫字典--表結(jié)構(gòu).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 小數(shù)位數(shù),
- CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空, ISNULL(e.text, '')
- AS 默認值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 創(chuàng)建時間,
- 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數(shù)據(jù)庫字典--表結(jié)構(gòu).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 小數(shù)位數(shù),
- CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允許空, ISNULL(e.text, '')
- AS 默認值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 創(chuàng)建時間,
- 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. SQL Server數(shù)據(jù)庫字典--索引.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 創(chuàng)建時間, 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. SQL Server數(shù)據(jù)庫字典--表.視圖.函數(shù).存儲過程.觸發(fā)器.主鍵.外鍵.約束.規(guī)則.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 '標量函數(shù)'
- WHEN 'IF' THEN '內(nèi)嵌表函數(shù)' WHEN 'P' THEN '存儲過程' WHEN 'PK' THEN 'PRIMARYKEY約束'
- WHEN 'RF' THEN '復(fù)制篩選存儲過程' WHEN 'S' THEN '系統(tǒng)表' WHEN 'TF' THEN '表函數(shù)'
- WHEN 'TR' THEN '觸發(fā)器' WHEN 'U' THEN '用戶表' WHEN 'UQ' THEN 'UNIQUE 約束'
- WHEN 'V' THEN '視圖' WHEN 'X' THEN '擴展存儲過程' WHEN 'R' THEN '規(guī)則' ELSE NULL
- END AS 類型, o.name AS 對象名, o.crdate AS 創(chuàng)建時間, 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')
關(guān)于使用SQL視圖查詢SQL Server數(shù)據(jù)庫字典的知識就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
【編輯推薦】
- SQL Server 2005數(shù)據(jù)庫鏡像相關(guān)知識概述
- 一個SQL Server數(shù)據(jù)庫刪除數(shù)據(jù)集中重復(fù)數(shù)據(jù)的例子
- SQLServer 2008 R2數(shù)據(jù)庫SSAS建模及擴展能力詳解
- 如何用觸發(fā)器實現(xiàn)記錄數(shù)據(jù)庫表和記錄更改日志的操作
- SQL Server數(shù)據(jù)庫使用DBCC ShowContig檢查索引碎片
網(wǎng)頁名稱：利用三個SQL視圖查出所有SQL Server數(shù)據(jù)庫字典
網(wǎng)站鏈接：http://www.5511xx.com/article/djsjsih.html
