快捷導(dǎo)航

列出SQL Server中具有默認(rèn)值的所有字段的語句

更新時間：2008年10月27日 16:09:28 作者：

上個星期我在對一個供應(yīng)商開發(fā)的數(shù)據(jù)庫按規(guī)定進(jìn)行故障排除的時候，我們需要對他們數(shù)據(jù)庫中50個表的每一個都進(jìn)行查看，以確保所有期望是默認(rèn)值的字段都被分配了默認(rèn)值。你可以想象這是一個多么令人畏懼的工作，而我立即提出了這個問題。有沒有一個比在SQL Server管理套件中打開每一個表來查看這個schema的更好方法嗎？

專家解答
　　通過查詢?nèi)魏螖?shù)據(jù)庫中的三個系統(tǒng)表，你可以獲得每個表的每一個字段的默認(rèn)值。下面是這個核心查詢。它返回分配給當(dāng)前數(shù)據(jù)庫中每個用戶表的默認(rèn)值。這個查詢在SQL 2000和SQL 2005中都是兼容的。
　　
Quote:

復(fù)制代碼代碼如下:

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
　　FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
　　LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id 
　　WHERE SO.xtype = 'U' 
　　ORDER BY SO.[name], SC.colid

　　sysobjects 為我們提供了表元數(shù)據(jù)。在這個例子中，我們只對表名稱感興趣。syscolumns 表存儲與每個表的各個字段相關(guān)聯(lián)的元數(shù)據(jù)。在這個例子中，我們只需要字段名稱。最后，默認(rèn)值元數(shù)據(jù)由syscomments表提供。
　　對Northwind數(shù)據(jù)庫運(yùn)行這個查詢生成下面的結(jié)果(為了簡短，省略了一些記錄)。注意，因?yàn)長EFT JOIN到syscomments表所以它將返回NULL默認(rèn)值。
按此在新窗口打開圖片

現(xiàn)在我在想這個很好的基本查詢版本有什么選擇。。。
　　選擇1：搜索特別的默認(rèn)值
　　通過編輯WHERE條件語句，我們可以在所有的表中查看特別的默認(rèn)值。
　　
Quote:
SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
　　FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
　　LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
　　WHERE SO.xtype = 'U' AND SM.TEXT = '(0)'
　　ORDER BY SO.[name], SC.colid
選擇2：只返回具有默認(rèn)值字段的信息
　　修改核心查詢的WHERE條件語句來忽略syscomments.text表中的NULL值，這個技巧如下所示：
　　
Quote:

復(fù)制代碼代碼如下:

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
　　FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
　　LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id 
　　WHERE SO.xtype = 'U' AND SM.TEXT IS NOT NULL 
　　ORDER BY SO.[name], SC.colid 

　　但是，將FROM條件從句中的JOIN從一個LEFT JOIN改為一個INNER JOIN會提供優(yōu)化：
　　
Quote:

復(fù)制代碼代碼如下:

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
　　FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
　　INNER JOIN dbo.syscomments SM ON SC.cdefault = SM.id 
　　WHERE SO.xtype = 'U' 
　　ORDER BY SO.[name], SC.colid 

　　其實(shí)還有另一個選擇，利用SQL 2005中的系統(tǒng)目錄視圖。前面的查詢給我提供了這時所需要的信息，并在SQL 2000和SQL 2005中都可以使用，在SQL2000實(shí)例中可以挖掘出與這個默認(rèn)值(實(shí)際上是一個默認(rèn)約束)關(guān)聯(lián)的額外元數(shù)據(jù)。通過將這個查詢特定在系統(tǒng)目錄視圖上，我們可以獲得在之前的查詢中沒有顯示出來的額外信息。
　　
Quote:

復(fù)制代碼代碼如下:

SELECT ST.[name] AS "Table Name", SC.[name] AS "Column Name", SD.definition AS "Default Value", SD.[name] AS "Constraint Name" 
　　FROM sys.tables ST INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id] 
　　INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id 
　　ORDER BY ST.[name], SC.colid 