SqlServer -辅助脚本

SELECT TOP 100 PERCENT --a.id, 
CASE
  WHEN a.colorder = 1 THEN d.NAME
  ELSE ''
END                                              AS 表名,
CASE
  WHEN a.colorder = 1 THEN Isnull(f.value, '')
  ELSE ''
END                                              AS 表说明,
a.colorder                                       AS 字段序号,
a.NAME                                           AS 字段名,
CASE
  WHEN Columnproperty(a.id, a.NAME, 'IsIdentity') = 1 THEN '√'
  ELSE ''
END                                              AS 标识,
CASE
  WHEN EXISTS (SELECT 1
               FROM   dbo.sysindexes si
                      INNER JOIN dbo.sysindexkeys sik
                              ON si.id = sik.id
                                 AND si.indid = sik.indid
                      INNER JOIN dbo.syscolumns sc
                              ON sc.id = sik.id
                                 AND sc.colid = sik.colid
                      INNER JOIN dbo.sysobjects so
                              ON so.NAME = si.NAME
                                 AND so.xtype = 'PK'
               WHERE  sc.id = a.id
                      AND sc.colid = a.colid) THEN '√'
  ELSE ''
END                                              AS 主键,
b.NAME                                           AS 类型,
a.length                                         AS 长度,
Columnproperty(a.id, a.NAME, 'PRECISION')        AS 精度,
Isnull(Columnproperty(a.id, a.NAME, 'Scale'), 0) AS 小数位数,
CASE
  WHEN a.isnullable = 1 THEN '√'
  ELSE ''
END                                              AS 允许空,
Isnull(e.text, '')                               AS 默认值,
Isnull(g.[value], '')                            AS 字段说明,
d.crdate                                         AS 创建时间,
CASE
  WHEN a.colorder = 1 THEN d.refdate
  ELSE NULL
END                                              AS 更改时间
FROM   dbo.syscolumns a
       LEFT OUTER JOIN dbo.systypes b
                    ON a.xtype = b.xusertype
       INNER JOIN dbo.sysobjects d
               ON a.id = d.id
                  AND d.xtype = 'U'
                  AND d.status >= 0
       LEFT OUTER JOIN dbo.syscomments e
                    ON a.cdefault = e.id
       LEFT OUTER JOIN sys.extended_properties g
                    ON a.id = g.major_id
                       AND a.colid = g.minor_id
                       AND g.NAME = 'MS_Description'
       LEFT OUTER JOIN sys.extended_properties f
                    ON d.id = f.major_id
                       AND f.minor_id = 0
                       AND f.NAME = 'MS_Description'
ORDER  BY d.NAME,
          字段序号

SELECT TOP 100 PERCENT --a.id, 
CASE
  WHEN b.keyno = 1 THEN c.NAME
  ELSE ''
END              AS 表名,
CASE
  WHEN b.keyno = 1 THEN a.NAME
  ELSE ''
END              AS 索引名称,
d.NAME           AS 列名,
b.keyno          AS 索引顺序,
CASE Indexkey_property(c.id, b.indid, b.keyno, 'isdescending')
  WHEN 1 THEN '降序'
  WHEN 0 THEN '升序'
END              AS 排序,
CASE
  WHEN p.id IS NULL THEN ''
  ELSE '√'
END              AS 主键,
CASE Indexproperty(c.id, a.NAME, 'IsClustered')
  WHEN 1 THEN '√'
  WHEN 0 THEN ''
END              AS 聚集,
CASE Indexproperty(c.id, a.NAME, 'IsUnique')
  WHEN 1 THEN '√'
  WHEN 0 THEN ''
END              AS 唯一,
CASE
  WHEN e.id IS NULL THEN ''
  ELSE '√'
END              AS 唯一约束,
a.origfillfactor AS 填充因子,
c.crdate         AS 创建时间,
c.refdate        AS 更改时间
FROM   dbo.sysindexes a
       INNER JOIN dbo.sysindexkeys b
               ON a.id = b.id
                  AND a.indid = b.indid
       INNER JOIN dbo.syscolumns d
               ON b.id = d.id
                  AND b.colid = d.colid
       INNER JOIN dbo.sysobjects c
               ON a.id = c.id
                  AND c.xtype = 'U'
       LEFT OUTER JOIN dbo.sysobjects e
                    ON e.NAME = a.NAME
                       AND e.xtype = 'UQ'
       LEFT OUTER JOIN dbo.sysobjects p
                    ON p.NAME = a.NAME
                       AND p.xtype = 'PK'
WHERE  ( Objectproperty(a.id, N'IsUserTable') = 1 )
       AND ( Objectproperty(a.id, N'IsMSShipped') = 0 )
       AND ( Indexproperty(a.id, a.NAME, 'IsAutoStatistics') = 0 )
ORDER  BY c.NAME,
          a.NAME,
          b.keyno