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

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

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
       ) a
ORDER BY
       ftable

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

DECLARE @TableName VARCHAR(50)
SET @TableName = 'BuildingInfo'

SELECT a.*,
       ISNULL(b.rtable, '0') AS RefTable,
       ISNULL(b.rname, '0') AS RefName
FROM   (
           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.fname
ORDER BY
       REPLACE(a.Name, '_', '')