通用SQL Server查询表结构脚本

 

工作中遇到一个需求，要查询表结构以及索引、主键，本来搞定了，可突然又不好使了，因为我第一次写的脚本只能兼容SQL Server 2005，用在SQL Server 2000里面就报错了，郁闷，我再改，这次是通用的了，看它还敢给我报错！

 

--查询索引和主键

SELECT
    IndexId     = idx.indid,
    IndexName   = idx.Name,
    ColumnName  = col.Name,
    Sort        = CASE INDEXKEY_PROPERTY(idx.id, idx.indid, idxk.keyno, 'IsDescending')
                  WHEN 1 THEN 'DESC'
                  WHEN 0 THEN 'ASC'
                  ELSE '' END,
    PrimaryKey  = CASE objPK.xtype
                  WHEN 'PK' THEN '√'
                  ELSE ''END
FROM
    sysindexes idx
    INNER JOIN sysobjects c
        ON idx.id=c.id
        AND c.xtype='U'
    LEFT JOIN sysobjects objPK
        ON objPK.[name]=idx.[name]
    LEFT JOIN sysindexkeys idxk
        ON idx.id = idxk.id
        AND idx.indid = idxk.indid
    LEFT JOIN syscolumns col
        ON col.colid = idxk.colid
        AND col.id = idxk.id
WHERE
    c.name='TableName' --这里改成表名
    AND col.Name IS NOT NULL

 

--查询表结构

SELECT
    ID           = col.colorder, 
    [Name]       = col.name,
    [SystemType] = types.name, 
    [Length]     = CAST(CASE WHEN types.name IN (N'nchar', N'nvarchar') AND col.length <> -1 THEN col.length/2 
                             ELSE col.length END
                        AS int)
FROM
    syscolumns col 
    LEFT JOIN systypes types
        ON col.xtype = types.xusertype 
    INNER JOIN sysobjects obj
        ON col.id     = obj.id
        AND obj.xtype = 'U'
        AND obj.name  <> 'dtproperties' 
WHERE
    obj.name='TableName'--这里改成表名

ORDER BY
    col.colorder