SqlServer查询表名的备注（查询表名描述 MS_Description）

查询表名描述 ms_description

  select tbs.name 表名,ds.value 描述       
  from sys.extended_properties ds  
  left join sysobjects tbs on ds.major_id=tbs.id  
  where  ds.minor_id=0 and  
  tbs.name='schedulerecords';--表名

添加表的描述

execute sp_addextendedproperty n'ms_description', n'菜单表', n'user', n'dbo', n'table', n'menus', null, null;

更新表的描述

execute sp_updateextendedproperty n'ms_description', n'菜单表', n'user', n'dbo', n'table', n'menus', null, null;

查询表的外键

  select tbs.name 表名,ds.value 描述      
  from sys.extended_properties ds  
  left join sysobjects tbs on ds.major_id=tbs.id  
  where  ds.minor_id=0 and   
  tbs.name='schedulerecords';--表名
  where object_name(sysobjects.parent_obj)='table name'

sql查询表的所有字段的备注说明

select
    tablename=case when c.column_id=1 then o.name else n'' end,
    tabledesc=isnull(case when c.column_id=1 then ptb.[value] end,n''),
    column_id=c.column_id,
    columnname=c.name,
    primarykey=isnull(idx.primarykey,n''),
    [identity]=case when c.is_identity=1 then n'√'else n'' end,
    computed=case when c.is_computed=1 then n'√'else n'' end,
    type=t.name,
    length=c.max_length,
    precision=c.precision,
    scale=c.scale,
    nullable=case when c.is_nullable=1 then n'√'else n'' end,
    [default]=isnull(d.definition,n''),
    columndesc=isnull(pfd.[value],n''),
    indexname=isnull(idx.indexname,n''),
    indexsort=isnull(idx.sort,n''),
    create_date=o.create_date,
    modify_date=o.modify_date
from sys.columns c 
inner join sys.objects o on c.[object_id]=o.[object_id] 
    and o.type='u' and o.is_ms_shipped=0
inner join sys.types t on c.user_type_id=t.user_type_id
left join sys.default_constraints d on c.[object_id]=d.parent_object_id 
    and c.column_id=d.parent_column_id and c.default_object_id=d.[object_id]
left join sys.extended_properties pfd on pfd.class=1 
    and c.[object_id]=pfd.major_id and c.column_id=pfd.minor_id
-- and pfd.name='caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
left join sys.extended_properties ptb on ptb.class=1 
    and ptb.minor_id=0 and c.[object_id]=ptb.major_id
-- and pfd.name='caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
left join -- 索引及主键信息
    (
    select
        idxc.[object_id],
        idxc.column_id,
        sort=case indexkey_property(idxc.[object_id],idxc.index_id,idxc.index_column_id,'isdescending')
        when 1 then 'desc' when 0 then 'asc' else '' end,
        primarykey=case when idx.is_primary_key=1 then n'√'else n'' end,
        indexname=idx.name
    from sys.indexes idx
    inner join sys.index_columns idxc on idx.[object_id]=idxc.[object_id]
        and idx.index_id=idxc.index_id
    left join sys.key_constraints kc on idx.[object_id]=kc.[parent_object_id]
        and idx.index_id=kc.unique_index_id
    inner join -- 对于一个列包含多个索引的情况,只显示第1个索引信息
        (
            select [object_id], column_id, index_id=min(index_id)
            from sys.index_columns
            group by [object_id], column_id
        ) idxcuq on idxc.[object_id]=idxcuq.[object_id]
            and idxc.column_id=idxcuq.column_id    and idxc.index_id=idxcuq.index_id
    ) idx on c.[object_id]=idx.[object_id]
    and c.column_id=idx.column_id
    --where o.name=n'tablename' -- 如果只查询指定表,加上此条件
order by o.name,c.column_id