SQL Server 中, 取得表的所有字段名和字段描述/注释

weixin_30454481

于 2009-12-02 12:01:00 发布

阅读量221

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/wuhen/articles/1615175.html

版权

本文提供了一种在SQLServer2000和SQLServer2005中查询表结构的方法，通过创建存储过程LookCol来获取指定表的所有列信息，包括列ID、列名、数据类型、长度、是否允许为空及注释等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL Server 2005:
create proc [dbo].[LookCol]
    @TableName nvarchar(50)
as
begin
    select c.ColID, c.name ColName, t.name TypeName, c.Length,c.IsNullable, p.value Note
    from syscolumns c
    inner join sysobjects o on o.id=c.id
    inner join systypes t on t.xusertype = c.xtype
    left join sys.extended_properties p on c.id=p.major_id and c.colid = p.minor_id
    where o.name=@TableName
    order by colid
end
SQL Server 2000:
create proc [dbo].[LookCol]
    @TableName nvarchar(50)
as
begin
    select
        c.ColID,
        c.name ColName,
        t.name TypeName,
        c.Length,
        c.IsNullable,
        p.value Note
    from
        syscolumns c
        inner join sysobjects o on o.id=c.id
        inner join systypes t on t.xusertype = c.xtype
        left join sysproperties p on c.id=p.id and c.colid = p.smallid
    where o.name=@TableName
    order by colid
end