SQL Server 獲取表結構完整元數據：欄位備註、主鍵、身分識別、預設值、精度、小數位 (2000/2005/2008+ 通用)

SQL Server 2000 版 (sysproperties 系統表)

SELECT 
    TableName      = CASE WHEN a.colorder=1 THEN d.name ELSE '' END,
    TableDesc      = CASE WHEN a.colorder=1 THEN ISNULL(f.value,'') ELSE '' END,
    ColOrder       = a.colorder,
    ColName        = a.name,
    IsIdentity     = CASE WHEN COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END,
    IsPK           = CASE WHEN EXISTS(
                          SELECT 1 FROM sysobjects 
                          WHERE xtype='PK' AND parent_obj=a.id 
                            AND name IN (SELECT name FROM sysindexes 
                                         WHERE indid IN (SELECT indid FROM sysindexkeys 
                                                         WHERE id=a.id AND colid=a.colid)))
                        THEN '√' ELSE '' END,
    TypeName       = b.name,
    Length         = a.length,
    Precision      = COLUMNPROPERTY(a.id,a.name,'Precision'),
    Scale          = ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    IsNullable     = CASE WHEN a.isnullable=1 THEN '√' ELSE '' END,
    DefaultValue   = ISNULL(e.text,''),
    ColDesc        = ISNULL(g.[value],'')
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype='U' AND d.name<>'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid
LEFT JOIN sysproperties f ON d.id = f.id AND f.smallid = 0
WHERE d.name = '你的表名'  -- 移除此行查全庫
ORDER BY a.id, a.colorder;

SQL Server 2005/2008+ 版 (sys.extended_properties 視圖)

USE YourDatabase;
GO

SELECT 
    TableName      = c.name,
    TableDesc      = ISNULL(f.value, ''),
    ColName        = a.name,
    ColOrder       = a.column_id,
    IsIdentity     = CASE WHEN a.is_identity = 1 THEN '√' ELSE '' END,
    IsPK           = CASE WHEN EXISTS(
                          SELECT 1 FROM sys.objects 
                          WHERE parent_object_id = a.object_id AND type = 'PK'
                            AND name IN (SELECT name FROM sys.indexes 
                                         WHERE index_id IN (SELECT index_id FROM sys.index_columns 
                                                            WHERE object_id = a.object_id AND column_id = a.column_id)))
                        THEN '√' ELSE '' END,
    TypeName       = b.name,
    MaxLength      = CASE 
                        WHEN a.max_length = -1 AND b.name <> 'xml' THEN 'MAX/2GB'
                        WHEN b.name = 'xml' THEN '2GB'
                        ELSE CAST(a.max_length AS VARCHAR)
                     END,
    Precision      = COLUMNPROPERTY(a.object_id, a.name, 'Precision'),
    Scale          = ISNULL(COLUMNPROPERTY(a.object_id, a.name, 'Scale'), 0),
    IsNullable     = CASE WHEN a.is_nullable = 1 THEN '√' ELSE '' END,
    ColDesc        = ISNULL(e.value, ''),
    DefaultValue   = ISNULL(d.definition, '')
FROM sys.columns a
LEFT JOIN sys.types b ON a.user_type_id = b.user_type_id
INNER JOIN sys.objects c ON a.object_id = c.object_id AND c.type = 'U'
LEFT JOIN sys.default_constraints d ON a.default_object_id = d.object_id
LEFT JOIN sys.extended_properties e ON e.major_id = c.object_id AND e.minor_id = a.column_id AND e.class = 1
LEFT JOIN sys.extended_properties f ON f.major_id = c.object_id AND f.minor_id = 0 AND f.class = 1
WHERE c.name = '你的表名'  -- 移除查全庫
ORDER BY c.name, a.column_id;

關鍵系統視圖/函數對照

輸出結果範例

擴展：加入索引資訊

-- 2005+ 版本追加索引欄位
LEFT JOIN (
    SELECT ic.object_id, ic.column_id, 
           STRING_AGG(i.name, ',') AS IndexNames,
           STRING_AGG(CASE WHEN i.is_unique=1 THEN 'UQ' ELSE 'IX' END, ',') AS IndexTypes
    FROM sys.index_columns ic
    JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.type_desc <> 'HEAP'
    GROUP BY ic.object_id, ic.column_id
) ix ON a.object_id = ix.object_id AND a.column_id = ix.column_id
-- SELECT 追加 ix.IndexNames, ix.IndexTypes