用于管理用户定义的数据库对象信息的系统表，此处对象包括用户表，存储过程，函数等

SQL Server 管理用户定义的数据库对象信息的系统表

### 获取用户定义表的字段信息

```SQL
SELECT     C.COLUMN_NAME AS '字段名'
         , T.NAME
           + CASE WHEN T.NAME IN ('nvarchar', 'nchar', 'varchar', 'char', 'varbinary', 'binary') THEN
                    '(' + CASE WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))END + ')'
                  ELSE ''
             END AS '类型'
         , CASE WHEN P.VALUE IS NULL THEN '' ELSE CAST(P.VALUE AS NVARCHAR(MAX))END AS '描述'
         , IC.MAX_LENGTH AS '占空间字节'
         , CASE WHEN T.NAME IN ('nvarchar', 'nchar', 'varchar', 'char', 'varbinary', 'binary') THEN
                  CASE WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))END
                ELSE CAST(IC.PRECISION AS VARCHAR(10))
           END AS '长度'
         , CASE WHEN C.IS_NULLABLE = 'YES' THEN '是' ELSE '否' END AS '是否允许为空'
         , ISNULL(COLUMN_DEFAULT, '') AS '默认值'
         , CASE WHEN KCU.COLUMN_NAME IS NULL THEN '' ELSE '√' END AS '主键'
         , CASE WHEN IC.is_identity = 1 THEN '√' ELSE '' END AS '标识'
         , CASE WHEN CCU.COLUMN_NAME IS NULL THEN '' ELSE '√' END AS '字段约束'
FROM       INFORMATION_SCHEMA.COLUMNS C
INNER JOIN SYS.TYPES T ON C.DATA_TYPE = T.NAME
INNER JOIN INFORMATION_SCHEMA.TABLES TB ON C.TABLE_NAME = TB.TABLE_NAME AND TB.TABLE_SCHEMA = 'dbo'
LEFT JOIN  INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.TABLE_SCHEMA = C.TABLE_SCHEMA
                                                      AND KCU.TABLE_NAME = C.TABLE_NAME
                                                      AND KCU.COLUMN_NAME = C.COLUMN_NAME
                                                      AND KCU.CONSTRAINT_NAME LIKE 'PK_%'
LEFT JOIN  SYS.IDENTITY_COLUMNS IC ON IC.OBJECT_ID = OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME) AND IC.NAME = C.COLUMN_NAME
LEFT JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_SCHEMA = C.TABLE_SCHEMA
                                                      AND TC.TABLE_NAME = C.TABLE_NAME
                                                      AND TC.CONSTRAINT_TYPE = 'CHECK'
                                                      AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON CCU.TABLE_SCHEMA = C.TABLE_SCHEMA
                                                             AND CCU.TABLE_NAME = C.TABLE_NAME
                                                             AND CCU.COLUMN_NAME = C.COLUMN_NAME
                                                             AND CCU.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN  SYS.EXTENDED_PROPERTIES P ON P.MAJOR_ID = OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME)
                                        AND P.MINOR_ID = C.ORDINAL_POSITION
                                        AND P.NAME = 'MS_Description'
WHERE      C.TABLE_SCHEMA = 'dbo' --替换成自己的表架构
           AND C.TABLE_NAME = 'mt_post_tbl' --只获取基本表
ORDER BY   C.TABLE_NAME, C.ORDINAL_POSITION
```

|字段名|类型|描述|占空间字节|长度|是否允许为空|默认值|主键|标识|字段约束|
|--|--|--|--|--|--|--|--|--|--|
|id|int||4|10|否||√|√|√|
|tmp_id|int||NULL|NULL|是|||||
|postId|int||NULL|NULL|是|||||
|positionName|varchar(100)||NULL|100|是|||||
|positionIntro|varchar(5000)||NULL|5000|是|||||
|parent_id|int||NULL|NULL|是|||||
|top_parent_id|int||NULL|NULL|是|||||
|parent_tree|varchar(500)||NULL|500|是|||||
|parent_names|varchar(1000)||NULL|1000|是|||||
|rule_codes|varchar(8000)||NULL|8000|是|||||
|deep|int||NULL|NULL|是|||||
|add_per|int||NULL|NULL|是|||||
|add_date|smalldatetime||NULL|NULL|是|||||
|del|int||NULL|NULL|否|||||
|del_date|smalldatetime||NULL|NULL|是|||||
|del_per|int||NULL|NULL|是|||||
|edit|int||NULL|NULL|是|||||
|edit_date|smalldatetime||NULL|NULL|是|||||
|edit_per|int||NULL|NULL|是|||||
|comId|int||NULL|NULL|是|||||
|dept_id|int||NULL|NULL|是|||||
|postArea|int||NULL|NULL|否|((0))||||
|ScopeOfFunctions|int|工作范围 0:未设置 1:买卖 2：租赁  3:均是|NULL|NULL|是|||||
|authorityRange|int|权限范围(数字字典中读取)：1房产顾问、2业务经理、3区域总监、4大区副总、5专员、6专家、7经理、8助理|NULL|NULL|否|((0))||||
|positionRank|int||NULL|NULL|是|||||
|postLevel|int|职位成长周期（目前用来判断经纪人V1-V4）|NULL|NULL|是|||||
|postRole|int||NULL|NULL|是|||||
|isRentMajor|int|职位角色；1：主营租赁、2：新房渠道经理、3：新房案场经理、4：新房销售经理、5：新房行政助理|NULL|NULL|否|||||
|isGrpMgr|int||NULL|NULL|否|((0))||||
|autoUpdateTime|timestamp|是否是业务组长；0：否、1：是|NULL|NULL|否|||||

### 获取数据库所有用户定义表结构

```SQL
SELECT     OBJECT_NAME(B.ID) 表名
         , B.COLORDER 序号
         , B.NAME 字段名称
         , C.NAME 字段类型
         , B.PREC 精度级别
         , B.SCALE 小数位数
         , CASE WHEN NOT F.ID IS NULL THEN 'TRUE' ELSE '' END 是否主键
         , CASE WHEN COLUMNPROPERTY(B.ID, B.NAME, 'ISIDENTITY') = 1 THEN 'TRUE' ELSE '' END AS 是否自动增长
         , CONVERT(VARCHAR(1000), ISNULL(G.VALUE, '')) 字段说明
FROM       SYSOBJECTS A
INNER JOIN SYSCOLUMNS B ON A.ID = B.ID
INNER JOIN SYSTYPES C ON B.XTYPE = C.XUSERTYPE
LEFT JOIN  SYSOBJECTS D ON B.ID = D.PARENT_OBJ AND D.XTYPE = 'PK'
LEFT JOIN  SYSINDEXES E ON B.ID = E.ID AND D.NAME = E.NAME
LEFT JOIN  SYSINDEXKEYS F ON B.ID = F.ID AND B.COLID = F.COLID AND E.INDID = F.INDID
LEFT JOIN  SYS.EXTENDED_PROPERTIES G ON B.ID = G.MAJOR_ID AND B.COLID = G.MINOR_ID
WHERE      A.XTYPE = 'U'
```

|表名|序号|字段名称|字段类型|精度级别|小数位数|是否主键|是否自动增长|字段说明|
|--|--|--|--|--|--|--|--|--|
|QuestionInfoTbl|2|questionCode|varchar|50|NULL|||咨询编号|
|ruleTimeSet|2|ruleCode|varchar|500|NULL|||功能点代码|
|ruleTimeSet|7|ruleCodeName|nvarchar|100|NULL|||功能点名称|
|PowerVerifiCodeInfoTbl|2|codeId|varchar|32|NULL|||标志id|
|PowerVerifiCodeInfoTbl|3|powerCode|varchar|20|NULL|||验证的权限功能点   2_56_6 责任盘3.0|
|PowerVerifiCodeInfoTbl|4|codeState|tinyint|3|0|||0待确认  1已确认|