SQLServer2008获取表结构信息

SELECT ISNULL(d.name,'') tablename,isnull(f.value,'') tablecomment,a.name zdm,CONVERT(VARCHAR(100), c.value) zdms,b.name,a.length,
(case when (SELECT count(*)  FROM sysobjects  WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND 
(indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))
) ))) AND (xtype = 'PK') )>0 then '是' else '否' end) N'ispk'
            FROM syscolumns a 
            left join systypes b ON a.xusertype = b.xusertype
            left join sys.extended_properties c on a.id=c.major_id AND a.colid = c.minor_id
            left   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'
            left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0
            where f.value is not null
            
            

--字段在那些存储过程中：

SELECT obj.Name 存储过程名,
sc.TEXT 存储过程内容  
FROM syscomments sc  
 INNER JOIN sysobjects obj ON sc.Id = obj.ID  
 WHERE sc.TEXT LIKE '%' + 'xh' + '%'  
 AND TYPE = 'P'  
 
 --查询字段在那些表
  select 
 sysobjects.name as tablename,
 syscolumns.name as columnname
 from syscolumns
  join sysobjects on sysobjects.id = syscolumns.id
 where syscolumns.name like '%ProductEntryId%'

SELECT
    d.name N'TableName',
    d.xtype N'TableType',
    a.colorder N'ColumnIndex',
    a.name N'ColumnName',
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IdnetityFG',
    (case when (SELECT count(*)  FROM sysobjects  WHERE 
               (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND 
                        (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND 
                                  (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))
                                  )
                        )
                        )
                ) AND (xtype = 'PK')
                )>0 then '1' else '0' end
    ) N'IsPK',
    b.name N'DataType',
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'Length',
    (case when a.isnullable=1 then '1'else '0' end) N'IsNullable',
    isnull(e.text,'') N'Default',
    g.value N'Description'    
FROM   syscolumns   a 
    left join systypes b on   a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id   and   d.xtype in ('U','V') and   d.name<>'dtproperties'
    left join syscomments e on a.cdefault=e.id
    LEFT OUTER JOIN sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id
order by object_name(a.id),a.colorder

SELECT a.name AS 字段名
    , CASE
        WHEN (
            SELECT COUNT(*)
            FROM sysobjects
            WHERE name IN (
                    SELECT name
                    FROM sysindexes
                    WHERE id = a.id
                        AND indid IN (
                            SELECT indid
                            FROM sysindexkeys
                            WHERE id = a.id
                                AND colid IN (
                                    SELECT colid
                                    FROM syscolumns
                                    WHERE id = a.id
                                        AND name = a.name
                                )
                        )
                )
                AND xtype = 'PK'
        ) > 0 THEN '√'
        ELSE ''
    END AS 主键, b.name AS 类型
    , CASE
        WHEN a.isnullable = 0 THEN '√'
        ELSE ''
    END AS 必填
    , isnull(g.[value], '') AS 字段描述
FROM syscolumns a
    LEFT JOIN systypes b ON a.xtype = 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 sys.extended_properties g 
 on a.id=g.major_id AND a.colid= g.minor_id  
 where d.name='MP_GuaranteeMoneyApply'--所要查询的表
 order by a.id,a.colorder

SELECT
 
        (case when a.colorder=1 then d.name else '' end)表名,
 
        a.colorder 字段序号,
 
        a.name 字段名,
 
        (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
 
       (case when (SELECT count(*)
 
        FROM sysobjects
 
        WHERE (name in
 
                (SELECT name
 
                FROM sysindexes
 
                WHERE (id = a.id) AND (indid in
 
                         (SELECT indid
 
                        FROM sysindexkeys
 
                        WHERE (id = a.id) AND (colid in
 
                                  (SELECT colid
 
                                 FROM syscolumns
 
                                 WHERE (id = a.id) AND (name = a.name))))))) AND
 
            (xtype = 'PK'))>0 then '√' else '' end) 主键,
 
       b.name 类型,
 
       a.length 占用字节数,
 
      COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
 
      isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
 
      (case when a.isnullable=1 then '√'else '' end) 允许空,
 
      isnull(e.text,'') 默认值,
 
      isnull(g.[value],'') AS 字段说明
 
FROM  syscolumns  a left join systypes b
 
on  a.xtype=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 sys.extended_properties g
 
on a.id=g.major_id AND a.colid = g.major_id
 
--WHERE d.name=N'表名'      -- 如果只查询指定表,加上此条件 
order by a.id,a.colorder