【整理】SqlServer查看表结构字段名/字段类型/字段描述/是否自增

SqlServer查看表结构(字段名/字段类型/字段描述/是否自增/是否可空/默认值)SqlServer查看表结构(字段名/字段类型/字段描述/是否自增/是否可空/默认值)

<pre name="code" class="sql">-- =============================================
-- Author:		<Author,SJL,Name>
-- Create date: <Create Date,2015-12-02 15:25:00,>
-- Description:	<Description,查询表描述及字段备注信息,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_desc](
	@table_name   varchar(100)-- 表名
	)
AS
BEGIN
	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,rn
END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!