SQL Server 2000/2005 查詢所有資料表與檢視結構的完整腳本

用途

快速產生資料字典，取得所有使用者資料表或檢視的欄位名稱、資料類型、長度、預設值、說明等中繼資料。

SQL Server 2000 版本

SELECT 
    Sysobjects.name AS TABLE_NAME, 
    syscolumns.Id, 
    syscolumns.name AS COLUMN_NAME,
    systypes.name AS DATA_TYPE, 
    syscolumns.length as CHARACTER_MAXIMUM_LENGTH, 
    sysproperties.[value] AS COLUMN_DESCRIPTION, 
    syscomments.text as COLUMN_DEFAULT,
    syscolumns.isnullable as IS_NULLABLE 
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype 
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id 
LEFT OUTER JOIN sysproperties ON 
    (sysproperties.smallid = syscolumns.colid AND sysproperties.id = syscolumns.id) 
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id 
WHERE syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') 
    AND (systypes.name <> 'sysname')
ORDER BY syscolumns.colid;

將紅字 'U' 改為 'V' 即可查詢檢視。

SQL Server 2005 版本

SELECT 
    Sysobjects.name AS TABLE_NAME, 
    syscolumns.Id, 
    syscolumns.name AS COLUMN_NAME,
    systypes.name AS DATA_TYPE, 
    syscolumns.length as CHARACTER_MAXIMUM_LENGTH, 
    sys.extended_properties.[value] AS COLUMN_DESCRIPTION, 
    syscomments.text as COLUMN_DEFAULT,
    syscolumns.isnullable as IS_NULLABLE 
FROM syscolumns
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype 
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id 
LEFT OUTER JOIN sys.extended_properties ON 
    (sys.extended_properties.minor_id = syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id) 
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id 
WHERE syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') 
    AND (systypes.name <> 'sysname')
ORDER BY syscolumns.colid;

主要差異在於擴展屬性系統表：sysproperties → sys.extended_properties，欄位 smallid/id → minor_id/major_id。

參考來源

DevX Tip 31235