三個(gè)SQL視圖查出所有SQL Server數(shù)據(jù)庫字典_Mssql數(shù)據(jù)庫教程
推薦：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ù).存儲(chǔ)過程.觸發(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 表名, 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 字段序號(hào), a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 標(biāo)識(shí), 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ù).存儲(chǔ)過程.觸發(fā)器.sql）
| 以下為引用的內(nèi)容： SELECT DISTINCT ORDER BY CASE o.xtype WHEN 'X' THEN '擴(kuò)展存儲(chǔ)過程' 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 '存儲(chǔ)過程' 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]
- sql 語句練習(xí)與答案
- 深入C++ string.find()函數(shù)的用法總結(jié)
- SQL Server中刪除重復(fù)數(shù)據(jù)的幾個(gè)方法
- sql刪除重復(fù)數(shù)據(jù)的詳細(xì)方法
- SQL SERVER 2000安裝教程圖文詳解
- 使用sql server management studio 2008 無法查看數(shù)據(jù)庫,提示 無法為該請求檢索數(shù)據(jù) 錯(cuò)誤916解決方法
- SQLServer日志清空語句(sql2000,sql2005,sql2008)
- Sql Server 2008完全卸載方法(其他版本類似)
- sql server 2008 不允許保存更改，您所做的更改要求刪除并重新創(chuàng)建以下表
- SQL Server 2008 清空刪除日志文件(瞬間日志變幾M)
- Win7系統(tǒng)安裝MySQL5.5.21圖解教程
- 將DataTable作為存儲(chǔ)過程參數(shù)的用法實(shí)例詳解
Mssql數(shù)據(jù)庫教程Rss訂閱編程教程搜索
Mssql數(shù)據(jù)庫教程推薦
- 開發(fā)環(huán)境下優(yōu)化SQl語句的十個(gè)重要步驟
- 怎樣從多個(gè)位置截取字符串的SQL語句
- SQL Server 數(shù)據(jù)庫清除日志的方法
- sql里將重復(fù)行數(shù)據(jù)合并為一行數(shù)據(jù)使用逗號(hào)進(jìn)行分隔
- 測試SQL Server業(yè)務(wù)規(guī)則鏈接方法
- 淺談SQL Server跟蹤數(shù)據(jù)實(shí)現(xiàn)索引優(yōu)化向?qū)?/a>
- 怎樣用SQL 2000 生成XML
- shp2sqlserver 用法簡析
- 解析配置SQL SERVER合并復(fù)制(一)概念介紹
- where條件順序不同、性能不同示例探討
猜你也喜歡看這些
- mysql基本操作
- MySQL 替換某字段內(nèi)部分內(nèi)容的UPDATE語句
- 網(wǎng)站模板:以數(shù)據(jù)庫字段分組顯示數(shù)據(jù)的sql語句
- 解析:內(nèi)聯(lián),左外聯(lián),右外聯(lián),全連接,交叉連接的區(qū)別
- Mysql的主從數(shù)據(jù)庫沒有同步的解決辦法
- 關(guān)于數(shù)據(jù)庫中保留小數(shù)位的問題
- MySQL為什么會(huì)索引失效？
- mysql 無法連接問題的定位和修復(fù)過程分享
- 如何用workbench導(dǎo)出mysql數(shù)據(jù)庫關(guān)系圖
- mysql密碼過期導(dǎo)致連接不上mysql
。