SqlServer2008中便捷查看表字段备注类型默认值的方法

概述可快速查看使用sys.sp_addextendedproperty创建的字段备注，及字段类型、默认值、是否可空等信息 EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注具体信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@l @H_404_1@

可快速查看使用sys.sp_addextendedproperty创建的字段备注，及字段类型、默认值、是否可空等信息

EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=N'备注具体信息',@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'table',@level1name=N'表名',@level2type=N'ColUMN',@level2name=N'列名'

创建如下存储过程：

-- =============================================-- Author:		<Author,shujudeliu,name>-- Create date: <Create Date,2017-07-18 17:40:00,>-- Description:	<Description,查询表描述及字段备注信息,>-- =============================================CREATE PROCEDURE [dbo].[proc_desc](	@table_name   varchar(100)-- 表名	)ASBEGIN	SET NOCOUNT ON;	select 类别,表名or字段名,描述,字段类型,是否自增,是否可空,默认值 from 	(	SELECT '表名' 类别,-1 column_ID,tbs.name 表名or字段名,ds.value 描述,'' 字段类型,'' 是否自增,'' 是否可空,'' 默认值,1 rn	FROM sys.extended_propertIEs ds  	left JOIN sysobjects tbs ON ds.major_ID=tbs.ID  	WHERE  ds.minor_ID=0 and tbs.name=@table_name	union	SELECT '------' 类别,'------------------------------------' 表名or字段名,'----------------------------------------------------------' 描述,'-----------------' 字段类型,'-----------' 是否自增,'-----------' 是否可空,'-------------' 默认值,2 rn	union	SELECT		'列名' 类别,c.column_ID,C.name 表名or字段名,s.value 描述,字段类型 = T.name + CASE T.user_type_ID WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')'     -- time			WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')'                                      -- datetime2			WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')'                                      -- datetimeoffset			WHEN 106 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'  -- decimal			WHEN 108 THEN '('+CAST(C.precision  AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')'  -- numeric			WHEN 165 THEN '('+ISNulL(CAST(NulliF(C.max_length,-1) AS VARCHAR),'MAX') +')'       -- varbinary			WHEN 167 THEN '('+ISNulL(CAST(NulliF(C.max_length,'MAX') +')'       -- varchar			WHEN 173 THEN '('+ISNulL(CAST(NulliF(C.max_length,'MAX') +')'       -- binary			WHEN 175 THEN '('+ISNulL(CAST(NulliF(C.max_length,'MAX') +')'       -- char			WHEN 231 THEN '('+ISNulL(CAST(NulliF(C.max_length,-1)/2 AS VARCHAR),'MAX') +')'     -- nvarchar(该字段校检根据实际情况)			WHEN 239 THEN '('+ISNulL(CAST(NulliF(C.max_length,'MAX') +')'       -- nchar			ELSE ''			END,cast(C.is_IDentity as varchar(10)) 是否自增,cast(C.is_nullable as varchar(10)) 是否可空,默认值 = ISNulL(STUFF(left(D.deFinition,LEN(D.deFinition)-1),1,''),3 rn	FROM sys.columns C	INNER JOIN sys.types T ON C.user_type_ID = T.user_type_ID 	left JOIN sys.default_constraints D ON D.[object_ID] =C.default_object_ID AND D.parent_object_ID = C.[object_ID] AND D.parent_column_ID = C.column_ID 	left join (select major_ID,minor_ID,value from sys.extended_propertIEs) s on s.major_ID = c.object_ID and s.minor_ID = c.column_ID	WHERE C.[object_ID] = OBJECT_ID(@table_name)	) s 	order by column_ID,rnEND

使用方法：

exec proc_desc 表名

示例：

总结

以上是内存溢出为你收集整理的SqlServer2008中便捷查看表字段备注类型默认值的方法全部内容，希望文章能够帮你解决SqlServer2008中便捷查看表字段备注类型默认值的方法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。