你的位置:首页 > 数据库

[数据库]sql 2012中获取表的信息,包含字段的描述


1.获取数据库中的表

select name from sysobjects where type='U'


2.获取表字段(此处是Route表)

Select name from syscolumns Where ID=OBJECT_ID('Route')


3.获取表中字段的描述

SELECT  value FROM  ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Route', 'column', default)


4.获取表的信息的所有信息如下图

select  b.[value] from sys.columns a left join sys.extended_properties b on a.object_id=b.major_idand a.column_id=b.minor_id inner join sysobjects c on a.column_id=c.idand a.[name]='列名' and c.[name]='表名'SELECT表名=case  when  a.colorder=1  then  d.name  else  ''  end,表说明=case  when  a.colorder=1  then  isnull(f.value,'')  else  ''  end,字段序号=a.colorder,字段名=a.name,标识=case  when  COLUMNPROPERTY(  a.id,a.name,'IsIdentity')=1  then  '√'else  ''  end,主键=case  when  exists(SELECT  1  FROM  sysobjects  where  xtype='PK'  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,类型=b.name,占用字节数=a.length,长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),允许空=case  when  a.isnullable=1  then  '√'else  ''  end,默认值=isnull(e.text,''),字段说明=isnull(g.[value],'')FROM  syscolumns  aleft  join  systypes  b  on  a.xusertype=b.xusertypeinner  join  sysobjects  d  on  a.id=d.id   and  d.xtype='U'  and   d.name<>'dtproperties'left  join  syscomments  e  on  a.cdefault=e.idleft  join  sys.extended_properties  g  on  a.id=g.major_id  and  a.colid=g.minor_idleft  join  sys.extended_properties  f  on  d.id=f.major_id  and  f.minor_id=0--where  d.name='orders'     --如果只查询指定表,加上此条件order  by  a.id,a.colorder

 

如下图

sql 2008中获取表的信息,包含字段的描述 - 茶茶猫 - 自己的路自己走...
 
5.获取一个表的表名,字段名,数据类型,字段说明的简写SQL:
SELECT   [TableName] = i_s.TABLE_NAME,   [ColumnName] = i_s.COLUMN_NAME,   [Description] = s.value,  [DataType]=i_s.DATA_TYPE FROM   INFORMATION_SCHEMA.COLUMNS i_s LEFT OUTER JOIN   sys.extended_properties s ON   s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)  AND s.minor_id = i_s.ORDINAL_POSITION  AND s.name = 'MS_Description' WHERE   OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0   AND i_s.TABLE_NAME = '表名' AND i_s.TABLE_SCHEMA='架构名'ORDER BY   i_s.TABLE_NAME, i_s.ORDINAL_POSITION