查询数据库字段主键，外键，唯一键等相关属性

查询表相关主外键属性：主表，主表字段，外表，外表字段

SELECT *FROM   (           SELECT a.*,                  b.fname           FROM   (                      SELECT OBJECT_NAME(rkeyid) rtable,                             col.name rname,                             OBJECT_NAME(fkeyid) ftable                      FROM   sysforeignkeys f                             INNER JOIN syscolumns col                                  ON  f.rkeyid = col.id                                  AND f.rkey = col.colid                  ) a                  RIGHT JOIN (                           SELECT OBJECT_NAME(rkeyid) rtable,                                  col.name fname,                                  OBJECT_NAME(fkeyid) ftable                           FROM   sysforeignkeys f                                  INNER JOIN syscolumns col                                       ON  f.fkeyid = col.id                                       AND f.fkey = col.colid                       ) b                       ON  a.rtable = b.rtable                       AND a.ftable = b.ftable       ) aORDER BY       ftable

查询字段名，表名，架构名，序号，是否主键，是否外键，是否唯一键，是否为空，是否自动增长，默认值，字段类型，大小，长度，小数位数，相关属性

DECLARE @TableName VARCHAR(50)SET @TableName = 'BuildingInfo'SELECT a.*,       ISNULL(b.rtable, '0') AS RefTable,       ISNULL(b.rname, '0') AS RefNameFROM   (           SELECT d.name AS TableName,--如果表名相同就返回空                     a.colorder AS ID,--字段序号                    a.name AS NAME,--字段名                  (                      CASE                            WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN                                 1                           ELSE 0                      END                  ) AS DbIdentity,--自动增长                     (                      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                  ) AS PK,--查询主键END                    (                      CASE                            WHEN (                                    SELECT COUNT(*)                                    FROM   (                                               SELECT OBJECT_NAME(f.fkeyid) AS                                                       fname,                                                      col.name,                                                      f.constid AS temp                                               FROM   syscolumns col,                                                      sysforeignkeys f                                               WHERE  f.fkeyid = col.id                                                      AND f.fkey = col.colid                                           ) ft                                    WHERE  ft.fname = d.name                                           AND ft.name = a.name                                ) > 0 THEN 1                           ELSE 0                      END                  ) AS FK,--查询外键                  (                      CASE                            WHEN (                                    SELECT COUNT(COLUMN_NAME)                                    FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE                                    WHERE  CONSTRAINT_NAME IN (SELECT NAME                                                               FROM   sys.key_constraints                                                               WHERE                                                                        OBJECT_NAME(parent_object_id) =                                                                       d.name                                                                      AND TYPE =                                                                           'UQ')                                           AND COLUMN_NAME = a.name                                ) > 0 THEN 1                           ELSE 0                      END                  ) AS UQ,--查询唯一键                  b.name AS DbType,--字段类型                     a.length AS DbLength,--占用字节数                  (                      SELECT TOP 1 TABLE_SCHEMA                      FROM   information_schema.COLUMNS                      WHERE  TABLE_NAME = d.name                  ) AS TableSchema,--架构                  COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS DbPrecision,-- 长度                  ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS DbScale,-- 小数位数                  (CASE WHEN a.isnullable = 1 THEN 1 ELSE 0 END) AS DbNull,--允许空                  ISNULL(e.text, '') AS DefaultValue --默认值           FROM   syscolumns a                  LEFT JOIN systypes b                       ON  a.xtype = b.xusertype                  INNER JOIN sysobjects d                       ON  a.id = d.id                       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 LIKE @TableName --所要查询的表       )a       LEFT JOIN (                SELECT a.*,                       b.fname                FROM   (                           SELECT OBJECT_NAME(rkeyid) rtable,                                  col.name rname,                                  OBJECT_NAME(fkeyid) ftable                           FROM   sysforeignkeys f                                  INNER JOIN syscolumns col                                       ON  f.rkeyid = col.id                                       AND f.rkey = col.colid                                       AND OBJECT_NAME(fkeyid) = '表名'                       ) a                       INNER JOIN (                                SELECT OBJECT_NAME(rkeyid) rtable,                                       col.name fname,                                       OBJECT_NAME(fkeyid) ftable                                FROM   sysforeignkeys f                                       INNER JOIN syscolumns col                                            ON  f.fkeyid = col.id                                            AND f.fkey = col.colid                                            AND OBJECT_NAME(fkeyid) = '表名'                            ) b                            ON  a.rtable = b.rtable            )b            ON  a.tablename = b.ftable            AND a.name = b.fnameORDER BY       REPLACE(a.Name, '_', '')