/**
本例设计的存储过程是完成对给定表的结构进行查询,包括表名、列名、数据类型、长度、字段说明。
**/
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'pSysSelectTable' AND type = 'P')
DROP PROCEDURE pSysSelectTable
GO
CREATE PROCEDURE pSysSelectTable
@User_Tablename VARCHAR(200)
AS
BEGIN
SELECT
[Table Name] = OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Data Type ] = t.Name,
[Data Length] = c.Max_Length,
[Description] = ex.value
FROM
sys.columns c
LEFT JOIN
Sys.Types t
ON
C.User_Type_ID = T.User_Type_ID
LEFT OUTER JOIN
sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = 'MS_Description'
WHERE
OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 AND OBJECT_NAME(c.object_id) = @User_Tablename
ORDER BY
OBJECT_NAME(c.object_id), c.column_id
END
一个读取表结构的存储过程
最新推荐文章于 2021-12-13 15:46:29 发布