sql: table,view,function, procedure created MS_Description in sql server

--添加描述 geovindu
--https://msdn.microsoft.com/en-us/library/ms180047.aspx
--https://msdn.microsoft.com/zh-cn/library/ms180047(v=sql.120).aspx
--https://msdn.microsoft.com/zh-cn/library/ms179853(v=sql.120).aspx


--为表添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '导入员工资料表', N'SCHEMA', N'dbo', N'table', N'StaffList', NULL, NULL
EXEC sp_updateextendedproperty N'MS_Description', '导入员工资料表', N'SCHEMA', N'dbo', N'table', N'StaffList', NULL, NULL
--为字段StaffId添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '序号', N'SCHEMA', N'dbo', N'table', N'StaffList', N'column', N'StaffId'
EXECUTE sp_addextendedproperty N'MS_Description', '员工编号', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffNo'
EXECUTE sp_updateextendedproperty N'MS_Description', '员工编号', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffNo'

EXECUTE sp_addextendedproperty N'MS_Description', '员工姓名', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffName'
EXECUTE sp_addextendedproperty N'MS_Description', '部门名称', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffDepartmnet'
EXECUTE sp_addextendedproperty N'MS_Description', '职位名称', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffPositionName'
EXECUTE sp_addextendedproperty N'MS_Description', '生成日期', N'user', N'dbo', N'table', N'StaffList', N'column', N'StaffAddate'

--获取某一个字段的描述
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'StaffList', 'column', 'StaffName')
---穫取一個錶所有字段的描述
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'StaffList', 'column', default)
--穫取錶的描述 涂聚文
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'StaffList', default, default)

--函数注释
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'部门函数.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = f_GetDepartmentName;
GO

--函数参数注释
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'部门函数.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'FUNCTION', @level1name = f_GetDepartmentName,
@level2type = N'PARAMETER', @level2name ='@did';
GO




select * from sys.extended_properties
go

--查询函数注释
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'FUNCTION', 'f_GetDepartmentName', default, default)
go
--函数参数注释
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'FUNCTION', 'f_GetDepartmentName', 'PARAMETER', default)
go


---视图描述
EXECUTE sp_addextendedproperty N'MS_Description', '员工资料表', N'SCHEMA', N'dbo', N'VIEW', N'View_InventoryReport', NULL, NULL
EXEC sp_updateextendedproperty N'MS_Description', '盘点报告视图', N'SCHEMA', N'dbo', N'VIEW', N'View_InventoryReport', NULL, NULL


---
--穫取视图的描述
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'VIEW', 'View_InventoryReport', default, default)

--
--存储过程注释
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'添加作者存储过程.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'PROCEDURE', @level1name = proc_Insert_AuthorList;
GO

--存储过程参数注释
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'添加作者存储过程作者名字参数.', 
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'PROCEDURE', @level1name = proc_Insert_AuthorList,
@level2type = N'PARAMETER', @level2name ='@AuthorName';
GO

select * from sys.extended_properties
go

--查询存储过程注释
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'proc_Insert_AuthorList', default, default)
go
--存储过程参数注释
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'proc_Insert_AuthorList', 'PARAMETER', default)
go

　　

 

--为函数添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '获取取回密码回答类型', N'user', N'dbo', N'function', N'fu_GetAnserTypeName', NULL, NULL

EXECUTE sp_addextendedproperty N'MS_Description', '输入ID号', N'user', N'dbo', N'function', N'fu_GetAnserTypeName', N'parameter', N'@did'

--查所有函數的描述
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'function', default, default, default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'function', 'fu_GetAnserTypeName', 'parameter', default)

--視圖描述
EXECUTE sp_addextendedproperty N'MS_Description', '公司信息视图', N'user', N'dbo', N'VIEW', N'View_CustomerList', NULL, NULL
EXECUTE sp_addextendedproperty N'MS_Description', 'ID号', N'user', N'dbo', N'view', N'View_CustomerList', N'column', N'CustomerCoNatureId'

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'view', default, default, default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'view', 'View_CustomerList', 'column', default)

--储存过程描述
EXECUTE sp_addextendedproperty N'MS_Description', '插入地址类型存储过程', N'user', N'dbo', N'procedure', N'proc_Insert_AddressTypeList', NULL, NULL

EXECUTE sp_addextendedproperty N'MS_Description', '输入地址类型名称', N'user', N'dbo', N'procedure', N'proc_Insert_AddressTypeList', N'parameter', N'@AddressTypeName'


SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'procedure', default, default, default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'procedure', 'proc_Insert_AddressTypeList', 'parameter', default)


SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'procedure', 'CreateEntrustByMRP', 'parameter', default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'APClericalCostMain', 'column', default)

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, default, default)

---存储过程式参数 （输入或输出）isoutparam 0:輸入 1:輸出
select * from syscolumns where ID in   
  (SELECT id FROM sysobjects as a 
   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1   
   and id = object_id(N'[dbo].[proc_Insert_AddressTypeListOutput]'))

--Geovin Du 20170329 涂聚文

　　

--http://www.sqlservercentral.com/articles/Metadata/72607/
--https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

 

---20170329 edit: Geovin Du  涂聚文

---1.SQL查询表的所有字段的备注说明
SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, 
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as 
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns 
INNER JOIN systypes 
    ON syscolumns.xtype = systypes.xtype 
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id 
  LEFT OUTER JOIN sys.extended_properties ON 
  ( sys.extended_properties.minor_id = syscolumns.colid 
    AND sys.extended_properties.major_id = syscolumns.id) 
  LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id 
  WHERE syscolumns.id IN 
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid
GO


--2.SQL查询表的所有字段的备注说明
SELECT 
(case when a.colorder=1 then d.name else '' end) N'表名', 
a.colorder N'字段序号', 
a.name N'字段名', 
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
end) N'标识', 
(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 '√' else '' end) N'主键', 
b.name N'类型', 
a.length N'占用字节数', 
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
(case when a.isnullable=1 then '√'else '' end) N'允许空', 
isnull(e.text,'') N'默认值', 
isnull(g.[value],'') AS N'字段说明' 
FROM syscolumns a 
left join systypes b 
on a.xtype=b.xusertype 
inner join sysobjects d 
on a.id=d.id and d.xtype='U' 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 
order by object_name(a.id),a.colorder
GO

--视图
SELECT 
(case when a.colorder=1 then d.name else '' end) N'表名', 
a.colorder N'字段序号', 
a.name N'字段名', 
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
end) N'标识', 
(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 '√' else '' end) N'主键', 
b.name N'类型', 
a.length N'占用字节数', 
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
(case when a.isnullable=1 then '√'else '' end) N'允许空', 
isnull(e.text,'') N'默认值', 
isnull(g.[value],'') AS N'字段说明' 
FROM syscolumns a 
left join systypes b 
on a.xtype=b.xusertype 
inner join sysobjects d 
on a.id=d.id and d.xtype='V' 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 
order by object_name(a.id),a.colorder
GO

---函數（表函数呢）
SELECT 
(case when a.colorder=1 then d.name else '' end) N'函数名', 
a.colorder N'参数序号', 
a.name N'参数名', 
isoutparam N'输入输出',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
end) N'标识', 
(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 '√' else '' end) N'主键', 
b.name N'类型', 
a.length N'占用字节数', 
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
(case when a.isnullable=1 then '√'else '' end) N'允许空', 
isnull(e.text,'') N'默认值', 
isnull(g.[value],'') AS N'参数说明' 
FROM syscolumns a 
left join systypes b 
on a.xtype=b.xusertype 
inner join sysobjects d 
on a.id=d.id and d.xtype='FN' 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 
order by object_name(a.id),a.colorder
GO


--存储过程
SELECT 
(case when a.colorder=1 then d.name else '' end) N'函数名', 
a.colorder N'参数序号', 
a.name N'参数名', 
isoutparam N'输入输出',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' 
end) N'标识', 
(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 '√' else '' end) N'主键', 
b.name N'类型', 
a.length N'占用字节数', 
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', 
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', 
(case when a.isnullable=1 then '√'else '' end) N'允许空', 
isnull(e.text,'') N'默认值', 
isnull(g.[value],'') AS N'参数说明' 
FROM syscolumns a 
left join systypes b 
on a.xtype=b.xusertype 
inner join sysobjects d 
on a.id=d.id and d.xtype='P' 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 
order by object_name(a.id),a.colorder
GO

---
SELECT id,xtype,name FROM sysobjects 


--http://www.sqlservercentral.com/articles/Metadata/72607/
--https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

--3. SQL 2005查询表的所有字段的备注说明（要考虑存储过，函数，视图，索引，事务等）
SELECT 
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U' --表
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1 
AND C.[object_id]=PFD.major_id 
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1 
AND PTB.minor_id=0 
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 
LEFT JOIN -- 索引及主键信息
(
SELECT 
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
 --WHERE O.name=N'geovindu' -- 如果只查询指定表,加上此条件
ORDER BY O.name,C.column_id
GO


--4.--sql server 2005,2008,2012
SELECT     
  表名=case   when   a.colorder=1   then   d.name   else   ''   end,   
  表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,   
  字段序号=a.colorder,   
  字段名=a.name,   
  标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,   
  主键=case   when   exists(SELECT   1   FROM   sys.sysobjects   where   xtype='PK'   and   name   in   (   
  SELECT   name   FROM   sys.sysindexes   WHERE   indid   in(   
  SELECT   indid   FROM   sys.sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid   
  )))   then   '√'   else   ''   end,   
  类型=b.name,   
  占用字节数=a.length,   
  长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   
  小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   
  允许空=case   when   a.isnullable=1   then   '√'else   ''   end,   
  默认值=isnull(e.text,''),   
  字段说明=isnull(g.[value],'')   
  FROM   sys.syscolumns   a   
  left   join   sys.systypes   b   on   a.xusertype=b.xusertype   
  inner   join   sys.sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'   
  left   join   sys.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   --major_id       
  left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0     
  --where   d.name='要查询的表'         --如果只查询指定表,加上此条件   
  order   by   a.id,a.colorder
GO


--5.--sql server 2000
SELECT     
  表名=case   when   a.colorder=1   then   d.name   else   ''   end,   
  表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,   
  字段序号=a.colorder,   
  字段名=a.name,   
  标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,   
  主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (   
  SELECT   name   FROM   sysindexes   WHERE   indid   in(   
  SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid   
  )))   then   '√'   else   ''   end,   
  类型=b.name,   
  占用字节数=a.length,   
  长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),   
  小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),   
  允许空=case   when   a.isnullable=1   then   '√'else   ''   end,   
  默认值=isnull(e.text,''),   
  字段说明=isnull(g.[value],'')   
  FROM   syscolumns   a   
  left   join   systypes   b   on   a.xusertype=b.xusertype   
  inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'   
  left   join   syscomments   e   on   a.cdefault=e.id   
  left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid       
  left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0  
   
  --where   d.name='要查询的表'         --如果只查询指定表,加上此条件   
  order   by   a.id,a.colorder
GO
---20170329 edit: Geovin Du  涂聚文

　　

--查询注释
select * from sys.extended_properties
go
--查询注释
SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder
go
--查询注释
select 
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = prop.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
    AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop 
    ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
    AND prop.minor_id = 0
    AND prop.name = 'MS_Description' 
WHERE tbl.table_type = 'base table'
go

--
SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE],T.TYPE_DESC
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE'
GO

--
SELECT
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = tableProp.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type,
    ColumnDescription = colDesc.ColumnDescription
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
LEFT JOIN sys.extended_properties tableProp 
    ON tableProp.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
        AND tableProp.minor_id = 0
        AND tableProp.name = 'MS_Description' 
LEFT JOIN (
    SELECT sc.object_id, sc.column_id, sc.name, colProp.[value] AS ColumnDescription
    FROM sys.columns sc
    INNER JOIN sys.extended_properties colProp
        ON colProp.major_id = sc.object_id
            AND colProp.minor_id = sc.column_id
            AND colProp.name = 'MS_Description' 
) colDesc
    ON colDesc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
        AND colDesc.name = col.COLUMN_NAME
WHERE tbl.table_type = 'base table'
--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT null
GO

--
SELECT
COL.COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE,
CONSTRAINT_TYPE,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsIdentity') IS_IDENTITY,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsComputed') IS_COMPUTED

FROM INFORMATION_SCHEMA.COLUMNS COL 
LEFT OUTER JOIN 
(
    SELECT COLUMN_NAME, CONSTRAINT_TYPE 
    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B 
    ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    WHERE A.TABLE_NAME = 'DuUserPermission'
) CONS
ON COL.COLUMN_NAME = CONS.COLUMN_NAME
WHERE COL.TABLE_NAME = 'DuUserPermission'
go

--https://github.com/TheDev6/SqlMeta
--http://stackoverflow.com/questions/887370/sql-server-extract-table-meta-data-description-fields-and-their-data-types

---存储过程或自定义函数
declare @ProcName nvarchar(100),@Schema nvarchar(100)
set @Schema='dbo'
set @ProcName='f_GetCardRecordIs' --proc_Insert_DuConfigOutput
SELECT
                    SCHEMA_NAME(schema_id) AS [Schema]
                ,   P.Name AS Name
                ,   @ProcName AS ProcedureName
                ,   TYPE_NAME(P.user_type_id) AS [ParameterDataType]
                ,   P.max_length AS [MaxLength]
                ,   P.Precision AS [Precision]
                ,   P.Scale AS Scale
                ,   P.has_default_value AS HasDefaultValue
                ,   P.default_value AS DefaultValue
                ,   P.object_id AS ObjectId
                ,   P.parameter_id AS ParameterId
                ,   P.system_type_id AS SystemTypeId
                ,   P.user_type_id AS UserTypeId
                ,   P.is_output AS IsOutput
                ,   P.is_cursor_ref AS IsCursor
                ,   P.is_xml_document AS IsXmlDocument
                ,   P.xml_collection_id AS XmlCollectionId
                ,   P.is_readonly AS IsReadOnly
                FROM sys.objects AS SO
                INNER JOIN sys.parameters AS P ON SO.object_id = P.object_id
                WHERE SO.object_id IN (SELECT
                            object_id
                        FROM sys.objects
                        WHERE type IN ('P', 'FN'))
                    AND (SO.Name = @ProcName
                        OR @ProcName IS NULL)
                    AND (SCHEMA_NAME(schema_id) = @Schema
                        OR @Schema IS NULL)
                ORDER BY P.parameter_id ASC
GO

SELECT name
                    FROM dbo.sysobjects
                    WHERE xtype = 'U' 
                    AND name <> 'sysdiagrams'
                    order by name asc
GO
declare @TableName nvarchar(100),@Schema nvarchar(100)
set @Schema='dbo'
set @TableName='DuDeptPermissionType'
SELECT
                    c.TABLE_CATALOG AS [TableCatalog]
                ,   c.TABLE_SCHEMA AS [Schema]
                ,   c.TABLE_NAME AS [TableName]
                ,   c.COLUMN_NAME AS [ColumnName]
                ,   c.ORDINAL_POSITION AS [OrdinalPosition]
                ,   c.COLUMN_DEFAULT AS [ColumnDefault]
                ,   c.IS_NULLABLE AS [Nullable]
                ,   c.DATA_TYPE AS [DataType]
                ,   c.CHARACTER_MAXIMUM_LENGTH AS [CharacterMaxLength]
                ,   c.CHARACTER_OCTET_LENGTH AS [CharacterOctetLenth]
                ,   c.NUMERIC_PRECISION AS [NumericPrecision]
                ,   c.NUMERIC_PRECISION_RADIX AS [NumericPrecisionRadix]
                ,   c.NUMERIC_SCALE AS [NumericScale]
                ,   c.DATETIME_PRECISION AS [DatTimePrecision]
                ,   c.CHARACTER_SET_CATALOG AS [CharacterSetCatalog]
                ,   c.CHARACTER_SET_SCHEMA AS [CharacterSetSchema]
                ,   c.CHARACTER_SET_NAME AS [CharacterSetName]
                ,   c.COLLATION_CATALOG AS [CollationCatalog]
                ,   c.COLLATION_SCHEMA AS [CollationSchema]
                ,   c.COLLATION_NAME AS [CollationName]
                ,   c.DOMAIN_CATALOG AS [DomainCatalog]
                ,   c.DOMAIN_SCHEMA AS [DomainSchema]
                ,   c.DOMAIN_NAME AS [DomainName]
                ,   IsPrimaryKey = CONVERT(BIT, (SELECT
                            COUNT(*)
                        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                            ,   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
                        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
                        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
                        AND tc.TABLE_NAME = c.TABLE_NAME
                        AND cu.TABLE_SCHEMA = c.TABLE_SCHEMA
                        AND cu.COLUMN_NAME = c.COLUMN_NAME)
                    )
                ,   IsIdentity = CONVERT(BIT, (SELECT
                            COUNT(*)
                        FROM sys.objects obj
                        INNER JOIN sys.COLUMNS col
                            ON obj.object_id = col.object_id
                        WHERE obj.type = 'U'
                        AND obj.Name = c.TABLE_NAME
                        AND col.Name = c.COLUMN_NAME
                        AND col.is_identity = 1)
                    )
                FROM INFORMATION_SCHEMA.COLUMNS c
                WHERE (@Schema IS NULL
                        OR c.TABLE_SCHEMA = @Schema)
                    AND (@TableName IS NULL
                        OR c.TABLE_NAME = @TableName)
 GO

declare @Table nvarchar(100),@Schema nvarchar(100)
set @Schema='dbo'
set @Table='DuDeptPermissionType'                   
 SELECT
                        KCU1.CONSTRAINT_NAME AS [ConstraintName]
                    ,   KCU1.TABLE_NAME AS [TableName]
                    ,   KCU1.COLUMN_NAME AS [ColumnName]
                    ,   KCU2.CONSTRAINT_NAME AS [UniqueConstraintName]
                    ,   KCU2.TABLE_NAME AS [UniqueTableName]
                    ,   KCU2.COLUMN_NAME AS [UniqueColumnName]
                    ,   RC.MATCH_OPTION AS [MatchOption]
                    ,   RC.UPDATE_RULE AS [UpdateRule]
                    ,   RC.DELETE_RULE AS [DeleteRule]
                    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
                    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
                        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
                        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
                        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
                        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
                    WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
                            AND (@Table IS NULL
                                OR KCU1.TABLE_NAME = @Table
                                OR KCU2.TABLE_NAME = @Table)
                            AND (@Schema IS NULL
                                OR KCU1.TABLE_SCHEMA = @Schema
                                OR KCU2.TABLE_SCHEMA = @Schema)
GO
declare @TableName nvarchar(100),@Schema nvarchar(100)
set @Schema='dbo'
set @TableName='DuDeptPermissionType'
SELECT
                    B.COLUMN_NAME
                FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
                    ,   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
                    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                    AND A.TABLE_NAME = @TableName
                    AND A.TABLE_SCHEMA = @Schema
GO