一种查询表详细属性的方法

一种查询表详细属性的方法

在SQL SERVER 中，我们可以通过系统表来查询一些有关表、字段、关键字、外键等明细信息，这些明细信息有助于我们进一步通过自动化模式实现行为处理、数据处理、结构处理等流程。
这里涉及到的表有：

SYS.Objects(系统对象表)

这张表中存储了所有对象及其的属性内容；包含数据库，包括本机编译标量用户定义函数中创建的每个用户定义的架构范围的对象的行。具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-objects-transact-sql

SYS.Columns(系统列对象表)

为包含列的对象（如视图或表）的每一列返回一行。 下面是包含列的对象类型的列表。
- 表值程序集函数 (FT)
- 内联表值 SQL 函数 (IF)
- 内部表 (IT)
- 系统表 (S)
- 表值 SQL 函数 (TF)
- 用户表 (U)
- 视图 (V)
具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-columns-transact-sql

SYS.Types()

具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-types-transact-sql
每个系统类型和用户定义类型都在表中对应一行。

SYS.default_constraints(默认约束)

包含每个对象都是默认定义 （而不是 CREATE DEFAULT 语句的 CREATE TABLE 或 ALTER TABLE 语句的一部分创建）
具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-default-constraints-transact-sql

sys.extended_properties(扩展属性)

具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties
针对当前数据库中的每个扩展属性返回一行。
可获得字段说明

SYS.foreign_key_columns(外键列表)

具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-foreign-key-columns-transact-sql
组成外键的每一列或列集在表中对应一行。

SYS.foreign_key(外键表)

包含每个对象，并且 FOREIGN KEY 约束
具体信息请参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-foreign-keys-transact-sql

COLUMNPROPERTY(元函数)

返回有关列或过程参数的信息。
检查列的确定性属性时，首先测试该列是否为计算列。IsDeterministic 为非计算列返回 NULL。
可以将计算列指定为索引列。
使用 COLUMNPROPERTY 访问元数据的权限应遵循根据权限访问元数据规则。
具体信息请参考：https://docs.microsoft.com/zh-cn/sql/t-sql/functions/columnproperty-transact-sql
元数据函数请参考：https://docs.microsoft.com/zh-cn/sql/t-sql/functions/metadata-functions-transact-sql

存储过程的实例

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'pSysSelectTable2' AND type = 'P')
   DROP PROCEDURE pSysSelectTable2
GO
CREATE PROCEDURE pSysSelectTable2
@User_Tablename VARCHAR(200)
AS
BEGIN
  SELECT
      表名        = CASE WHEN A.Column_ID = 1 THEN D.Name ELSE '' END,
      表说明      = CASE WHEN A.Column_ID = 1 THEN ISNULL(F.value,'') ELSE '' END,
      字段序号    = A.Column_ID,
      字段名      = A.Name,
      标识        = CASE WHEN COLUMNPROPERTY(A.Object_ID,A.Name,'IsIdentity') = 1 THEN '√' ELSE '' END,
      主键        = CASE WHEN Exists
                    (
                      SELECT 1 FROM SYS.Objects WHERE type = 'PK' AND parent_object_id = A.Object_ID
                      AND Name IN 
                      (
                        SELECT name FROM sys.indexes
                        WHERE index_id in 
                        (
                          SELECT index_id FROM sys.index_columns WHERE object_id = A.object_id AND index_column_id = A.Column_ID
                        )
                      )
                    ) then '√' else '' end,
      类型        = B.Name,
      占用字节数  = A.Max_Length,
      长度        = COLUMNPROPERTY(A.Object_ID,A.Name,'PRECISION'),
      小数位数    = ISNULL(COLUMNPROPERTY(A.Object_ID,A.Name,'Scale'),0),
      允许空      = CASE WHEN A.is_nullable = 1 THEN '√' ELSE '' END,
      默认值      = ISNULL(E.definition,''),
      字段说明    = ISNULL(G.VALUE,''),
      外键名称    = ISNULL(FK.name,''),
      外键表名    = ISNULL(FKT.Name,''),
      外键列名    = ISNULL(FC.Name,''),
      外键列ID    = ISNULL(FC.Column_id,0)
  FROM
    SYS.Columns A
  LEFT JOIN
    SYS.Types B                 ON A.user_type_id = B.user_type_id
  INNER JOIN
    SYS.Objects D               ON A.Object_ID = D.Object_ID AND D.type = 'U' AND D.Name = @User_Tablename
  LEFT JOIN
    SYS.default_constraints E   ON A.default_object_id = E.object_id
  LEFT JOIN
    sys.extended_properties G   ON A.Object_ID = G.major_id AND A.Column_ID = G.minor_id
  LEFT JOIN
    sys.extended_properties F   ON A.Object_ID = F.major_id and F.minor_id = 0
  -- 外键开始
  LEFT JOIN
    SYS.foreign_key_columns FKC ON A.object_id = FKC.parent_object_id AND A.Column_id = FKC.parent_column_id
  LEFT JOIN
    SYS.foreign_keys FK         ON A.object_id = FK.parent_object_id AND FK.object_id = FKC.constraint_object_id
  LEFT JOIN
    SYS.columns FC              ON FKC.referenced_object_id = FC.object_id AND FKC.referenced_column_id = FC.column_id
  LEFT JOIN
    SYS.objects FKT             ON FKC.referenced_object_id = FKT.object_id
  -- 外键结束
  ORDER BY A.Column_ID
END

查询结果如下：

------------------------------------------------------------------------------
  表名        表说明            字段序号  字段名        标识  主键
------------------------------------------------------------------------------
1 App_Officer 供应商官员信息表  1         fSupplier_ID        √     
2                               2         fID                 √     
3                               3         fName               
4                               4         fPhone              
5                               5         fPosition           
------------------------------------------------------------------------------
  类型    占用字节数 长度  小数位数  允许空 默认值    
------------------------------------------------------------------------------
1 int     4          10    0
2 int     4          10    0
3 varchar 20         20    0         √      ('PAUL')  
4 varchar 80         80    0         √
5 varchar 80         80    0         √
------------------------------------------------------------------------------
  字段说明   外键名称                    外键表名      外键列名      外键列ID
------------------------------------------------------------------------------
1 供应商序号 FK_App_Officer_fSupplier_ID App_Supplier  fSupplier_ID  1
2 序号
3 姓名
4 电话
5 职位
------------------------------------------------------------------------------