获取指定表中列的数据类型

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_COLS_TYPE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 Drop PROCEDURE [dbo].[TBL_COLS_TYPE]
GO
CREATE PROCEDURE TBL_COLS_TYPE
@tblname nvarchar(256)
AS
SELECT ISNULL(ISPK.index_name,'') AS 主键名称
 ,CASE ISNULL(ISPK.is_identity,0) WHEN 0 THEN '否' ELSE '是' END AS 标识列
 ,c.name AS 列名称
    ,c.column_id AS 列序号
    ,SCHEMA_NAME(t.schema_id) AS 构架名称
    ,t.name AS 数据类型
    ,CASE t.is_user_defined WHEN 0 THEN '否' ELSE '是' END AS 自定义类型
    ,CASE t.is_assembly_type WHEN 0 THEN '否' ELSE '是' END AS 基元数据类型
    ,c.max_length AS 最大长度
    ,c.precision 精度
    ,c.scale 小数位数
FROM sys.columns AS c
JOIN sys.types AS t
 ON c.user_type_id=t.user_type_id
LEFT JOIN (SELECT i.name AS index_name
    ,c.name AS column_name
    ,c.column_id AS column_id
    ,is_identity
   FROM sys.indexes AS i
   INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
   INNER JOIN sys.columns AS c
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
   WHERE i.is_primary_key = 1
    AND i.object_id = OBJECT_ID(''+@tblname+'')) ISPK
 ON c.column_id = ISPK.column_id
WHERE c.object_id = OBJECT_ID(''+@tblname+'')
ORDER BY c.column_id;

GO

 

--测试结果

exec TBL_COLS_TYPE 'dbo.vacation'