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
推荐阅读
-
oracle 查询表名以及表的列名
-
SQLSERVER查询锁表,阻塞,使用表名查询存储过程,行数,表字段类型等常规查询实例讲解
-
flask/django 动态查询表结构相同表名不同数据的Model实现方法
-
SQL根据表名,查询表所有字段(列名)的教程
-
Oracle查询库中所有表名、字段名、字段名说明,查询表的数据条数、表名、中文表名等实例教程
-
sql查询当前数据库的所有表名
-
Oralce查询某一用户下所有表名带下划线_的空表
-
Sql查询MySql数据库中的表名和描述表中字段(列)信息
-
oracle查询所有用户表的表名、主键名称、索引、外键、修改密码等操作详解
-
使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名,字段名