通用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