數(shù)據(jù)庫:SQL2k/2005查詢表的結(jié)構(gòu)信息

SQL2000下執(zhí)行語句：
    SELECT
    sysobjects.name AS [表名],
    sysproperties.[value] AS [表說明],
    syscolumns.name AS [列名],
    properties.[value] AS [字段說明],
    systypes.name AS [字段類型],
    syscolumns.length AS [長度],
    ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [小數(shù)位數(shù)],
    CASE syscolumns.isnullable WHEN '1' THEN 'Y' ELSE 'N' END AS [是否允許空值],
    CASE WHEN syscomments.text IS NULL THEN '' ELSE syscomments.text END AS [默認(rèn)值],
    CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS [自增標(biāo)識],
    CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN
    (SELECT name
    FROM sysindexes
    WHERE indid IN
    (SELECT indid
    FROM sysindexkeys
    WHERE id = syscolumns.id AND colid = syscolumns.colid)))
    THEN '√' ELSE '' END AS [主鍵]
    FROM syscolumns INNER JOIN
    sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
    systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
    sysproperties properties ON syscolumns.id = properties.id AND
    syscolumns.colid = properties.smallid LEFT OUTER JOIN
    sysproperties ON sysobjects.id = sysproperties.id AND
    sysproperties.smallid = 0 LEFT OUTER JOIN
    syscomments ON syscolumns.cdefault = syscomments.id
    WHERE (sysobjects.xtype = 'U')
    --AND sysobjects.NAME='表名' --只查詢某個(gè)表的結(jié)構(gòu),否則將本行注釋掉就是全庫
    ORDER BY [表名],[列名]
    --SQL2005下執(zhí)行語句：
    SELECT
    Sysobjects.name AS [表名],
    syscolumns.name AS [列名],
    systypes.name AS [數(shù)據(jù)類型],
    syscolumns.length AS [字段長度],
    sys.extended_properties.[value] AS [字段描述],
    syscomments.text AS [默認(rèn)值],
    CASE syscolumns.isnullable WHEN '1' THEN 'Y' ELSE 'N' END AS [是否允許空值],
    ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [小數(shù)位數(shù)],
    COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') AS [自增標(biāo)識] ,
    CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN
    (SELECT name
    FROM sysindexes
    WHERE indid IN
    (SELECT indid
    FROM sysindexkeys
    WHERE id = syscolumns.id AND colid = syscolumns.colid)))
    THEN '√' ELSE '' END AS [主鍵]
    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 (systypes.name <> 'sysname')
    -- AND syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U' AND NAME = '表名') --僅查某表，否則注釋掉本行為全庫
    ORDER BY [表名],[列名]