sqlserver查询列名、注释类型、长度、是否为空

时间:2024-11-08 07:21:00
SELECT 
    表名       = case when =1 then else '' end,
    表说明     = case when =1 then isnull(,'') else '' end,
    字段序号   = ,
    字段名     = ,
    标识       = case when COLUMNPROPERTY( ,,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj= and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = AND colid=))) then '√' else '' end,
    类型       = ,
    占用字节数 = ,
    长度       = COLUMNPROPERTY(,,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(,,'Scale'),0),
    允许空     = case when =1 then '√'else '' end,
    默认值     = isnull(,''),
    字段说明   = isnull(g.[value],'')
FROM 
    syscolumns a
left join 
    systypes b 
on 
    =
inner join 
    sysobjects d 
on 
    =  and ='U' and  <>'dtproperties'
left join 
    syscomments e 
on 
    =
left join 
sys.extended_properties   g 
on 
    =G.major_id and =g.minor_id  
left join
sys.extended_properties f
on 
    =f.major_id and f.minor_id=0
where 
    ='tableName'    --如果只查询指定表,加上此红色where条件,tablename是要查询的表名;去除红色where条件查询说有的表信息
order by 
    ,