欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SqlServer查询表名的备注(查询表名描述 MS_Description)

程序员文章站 2022-03-08 23:22:16
查询表名描述 ms_description select tbs.name 表名,ds.value 描述 from sys.extended_properties ds...

查询表名描述 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