分享:sp_type返回表结构字段类型及长度等
[sql]
USE [master]
GO
IF OBJECT_ID('sp_type','P') IS NOT NULL
DROP PROCEDURE [sp_type]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************** object: sp_type script_datetime: 2013-05-07 14:34:00****************/
CREATE PROC sp_type (@tab NVARCHAR(128))
AS
/*
作者：陈恩辉-弘恩
说明：可以简单返回表的数据类型，对数据长度精度未做详细处理
*/
IF object_id(@tab, 'U') IS NULL
BEGIN
PRINT '在
数据库：'+ db_name() + ' 中，表：' + @tab + ' 不存在！'
RETURN
END
DECLARE @sqlstr VARCHAR(MAX) ;
SET @sqlstr = ' SELECT '
+ STUFF(( SELECT ',' + name + ' = ''' + type_name + ''''
FROM ( SELECT name ,
TYPE_NAME(system_type_id)
+ CASE WHEN TYPE_NAME(system_type_id) LIKE '%char'
THEN '('
+ CAST(CASE WHEN max_length < 0
THEN 'max'
ELSE max_length
END AS VARCHAR) + ')'
ELSE ''
END type_name ,
column_id
FROM sys.columns
WHERE object_id = OBJECT_ID(@tab, 'U')
) t
ORDER BY t.column_id
FOR
XML PATH('')
), 1, 1, '')
EXEC( @sqlstr )
GO
EXEC sp_MS_marksystemobject '[sp_type]'
GO