指定用户表获取表columns信息

 工作需要写了一个函数获取表结构备注信息.

 

declare @objectName varchar(50)
    set @objectName='AttributeRelation'
    select 
    名称    =    a.name,
    数据类型=    c.name,
    长度    =    a.length,
    主键    =    case when exists(
                SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                SELECT name FROM sysindexes WHERE indid in(
                SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    默认值    =    d.text,
    允许空    =    case when a.isnullable=1 then '√' else '' end,
    说明    =    isnull(p.value,'') 
    from syscolumns as a 
    inner join sysobjects as b on b.id=a.id and b.xtype='U'
    inner join systypes as c on c.xusertype=a.xusertype
    left join syscomments as d on d.id=a.cdefault
    left join sys.extended_properties as p on a.id=p.major_id and a.colid=p.minor_id
    where b.[name]=@objectName
    order by a.colorder

 

 

执行效果