获取表信息的存储过程

spt110

于 2011-06-07 11:57:00 发布

阅读量439

点赞数

CC 4.0 BY-SA版权

文章标签：存储 properties join sql object

本文链接：https://blog.youkuaiyun.com/spt110/article/details/6529262

本文介绍了一个 SQL Server 存储过程，用于查询指定表的所有列名称、数据类型、长度、是否允许为空及列描述等详细信息。此过程通过 syscolumns、systypes 和 sys.extended_properties 等系统表来获取所需信息。

create procedure sp_GetTableInfo @tableName nvarchar(20)
as
begin
declare @sql nvarchar(500);
set @sql='select a.name as ColumnName,b.name as DataTypeName
,a.length,a.isnullable
,MS_Description.value as Description
from syscolumns as a
left join systypes as b on a.xtype = b.xusertype
left join sys.extended_properties as MS_Description
on MS_Description.major_id=a.ID and MS_Description.minor_id=a.colid
Where a.ID=OBJECT_ID('''+@tableName+''')';
exec sp_executesql @sql
end