--添加描述 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, NULLEXEC 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';GOselect * from sys.extended_propertiesgo--查询函数注释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, NULLEXEC 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';GOselect * from sys.extended_propertiesgo--查询存储过程注释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, NULLEXECUTE 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, NULLEXECUTE 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, NULLEXECUTE 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.colidGO--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.colorderGO--视图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.colorderGO---函數（表函数呢）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.colorderGO--存储过程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.colorderGO---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_dateFROM sys.columns CINNER JOIN sys.objects OON C.[object_id]=O.[object_id]AND O.type='U' --表AND O.is_ms_shipped=0INNER JOIN sys.types TON C.user_type_id=T.user_type_idLEFT JOIN sys.default_constraints DON C.[object_id]=D.parent_object_idAND C.column_id=D.parent_column_idAND C.default_object_id=D.[object_id]LEFT JOIN sys.extended_properties PFDON 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 PTBON 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.NameFROM sys.indexes IDXINNER JOIN sys.index_columns IDXCON IDX.[object_id]=IDXC.[object_id]AND IDX.index_id=IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.[object_id]=KC.[parent_object_id]AND IDX.index_id=KC.unique_index_idINNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息(SELECT [object_id], Column_id, index_id=MIN(index_id)FROM sys.index_columnsGROUP BY [object_id], Column_id) IDXCUQON IDXC.[object_id]=IDXCUQ.[object_id]AND IDXC.Column_id=IDXCUQ.Column_idAND IDXC.index_id=IDXCUQ.index_id) IDXON C.[object_id]=IDX.[object_id]AND C.column_id=IDX.column_id --WHERE O.name=N'geovindu' -- 如果只查询指定表,加上此条件ORDER BY O.name,C.column_idGO--4.--sql server 2005,2008,2012SELECT 表名=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.colorderGO--5.--sql server 2000SELECT 表名=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.colorderGO---20170329 edit: Geovin Du 涂聚文
--查询注释select * from sys.extended_propertiesgo--查询注释SELECT u.name + '.' + t.name AS [table], td.value AS [table_desc], c.name AS [column], cd.value AS [column_desc]FROM sysobjects tINNER JOIN sysusers u ON u.uid = t.uidLEFT 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.idLEFT 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.colordergo--查询注释select TableName = tbl.table_schema + '.' + tbl.table_name, TableDescription = prop.value, ColumnName = col.column_name, ColumnDataType = col.data_typeFROM information_schema.tables tblINNER JOIN information_schema.columns col ON col.table_name = tbl.table_name AND col.table_schema = tbl.table_schemaLEFT 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_DESCFROM SYS.OBJECTS AS TJOIN SYS.COLUMNS AS CON T.OBJECT_ID=C.OBJECT_IDJOIN SYS.TYPES AS PON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_IDWHERE 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.ColumnDescriptionFROM information_schema.tables tblINNER JOIN information_schema.columns col ON col.table_name = tbl.table_nameLEFT 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_NAMEWHERE tbl.table_type = 'base table'--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT nullGO--SELECTCOL.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_COMPUTEDFROM 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') CONSON COL.COLUMN_NAME = CONS.COLUMN_NAMEWHERE 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_DuConfigOutputSELECT 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 ASCGOSELECT name FROM dbo.sysobjects WHERE xtype = 'U' AND name <> 'sysdiagrams' order by name ascGOdeclare @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) GOdeclare @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)GOdeclare @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 = @SchemaGO