三個(gè)SQL視圖查出所有SQL Server數(shù)據(jù)庫字典_Mssql數(shù)據(jù)庫教程
      
編輯Tag賺U幣
      


      教程Tag：暫無Tag,歡迎添加,賺取U幣!
      


      

      推薦：SQL Server連接中常見錯(cuò)誤的解決方法
      一.SQL Server不存在或訪問被拒絕 分析：此問題最為復(fù)雜，由于錯(cuò)誤發(fā)生的原因比較多,所以需要檢查很多方面。 一般情況下,有幾種可能性: 1.SQL Server名稱或IP地址拼寫有誤。 2.服務(wù)器端網(wǎng)絡(luò)配置有誤。 3.客戶端網(wǎng)絡(luò)配置有誤。 要解決此問題,我們一般要遵循
      
		
			
      本文主要介紹了三個(gè)實(shí)用的SQL Server數(shù)據(jù)庫字典SQL語句，數(shù)據(jù)庫字典主要包括表結(jié)構(gòu)(分為SQL Server 2000和SQL Server 2005)、索引和主鍵. 外鍵.約束.視圖.函數(shù).存儲過程.觸發(fā)器。你可以在查詢分析器、企業(yè)管理器中簡單執(zhí)行后，快速的查出SQL Server 2000及SQL Server 2005的全部數(shù)據(jù)字典。 

      

      

      1. SQL Server 2000數(shù)據(jù)庫字典（表結(jié)構(gòu).sql） 

      

      

      

      


      
以下為引用的內(nèi)容：
      

      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 標(biāo)識, 
      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 默認(rèn)值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 創(chuàng)建時(shí)間, 
      CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改時(shí)間 


      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 

      

      ◆SQL Server 2005數(shù)據(jù)庫字典（表結(jié)構(gòu).sql） 
      

      

      


      
以下為引用的內(nèi)容：
      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 標(biāo)識, 
      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 默認(rèn)值, ISNULL(g.[value], '') AS 字段說明, d.crdate AS 創(chuàng)建時(shí)間, 
      CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改時(shí)間
      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
      

      2.SQL Server數(shù)據(jù)庫字典（索引.sql） 

      

      

      

      


      
以下為引用的內(nèi)容：
      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)建時(shí)間, c.refdate AS 更改時(shí)間
      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ā)器.sql） 

      

      

      

      


      
以下為引用的內(nèi)容：
      

      SELECT DISTINCT 
      TOP 100 PERCENT o.xtype, 
      CASE o.xtype WHEN 'X' THEN '擴(kuò)展存儲過程' WHEN 'TR' THEN '觸發(fā)器' WHEN 'PK' THEN
      '主鍵' WHEN 'F' THEN '外鍵' WHEN 'C' THEN '約束' WHEN 'V' THEN '視圖' WHEN 'FN'
      THEN '函數(shù)-標(biāo)量' WHEN 'IF' THEN '函數(shù)-內(nèi)嵌' WHEN 'TF' THEN '函數(shù)-表值' ELSE '存儲過程'
      END AS 類型, o.name AS 對象名, o.crdate AS 創(chuàng)建時(shí)間, o.refdate AS 更改時(shí)間, 
      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) 


      ORDER BY CASE o.xtype WHEN 'X' THEN '擴(kuò)展存儲過程' WHEN 'TR' THEN '觸發(fā)器' WHEN
      'PK' THEN '主鍵' WHEN 'F' THEN '外鍵' WHEN 'C' THEN '約束' WHEN 'V' THEN '視圖'
      WHEN 'FN' THEN '函數(shù)-標(biāo)量' WHEN 'IF' THEN '函數(shù)-內(nèi)嵌' WHEN 'TF' THEN '函數(shù)-表值'
      ELSE '存儲過程' END DESC 
      
	
      
	
	
      分享：一個(gè)獲取SQL Server數(shù)據(jù)字典的經(jīng)典SQL語句
      本文主要介紹了一個(gè)獲取SQL Server數(shù)據(jù)字典的經(jīng)典SQL語句，大家可以根據(jù)各自的實(shí)際情況對這段語句進(jìn)行相應(yīng)的修改。 以下為引用的內(nèi)容： SELECT sysobjects.name AS [table], sysproperties.[value] AS 表說明, syscolumns.name AS field, properties.[value]
      


      

      


      來源：模板無憂//所屬分類：Mssql數(shù)據(jù)庫教程/更新時(shí)間：2012-06-10


      


      



      


      



      相關(guān)Mssql數(shù)據(jù)庫教程：
      




      

      Mssql數(shù)據(jù)庫教程Rss訂閱編程教程搜索