sys.tables - 元数据表

image.png

获取如下数据

  1. SELECT 
  2. a.name FieldName,
  3.                 (case a.name when 'ID' then '[int] IDENTITY(1,1)' else 
  4.                 (case b.name when 'int' then '[int]' when 'bit' then '[bit]' when 'bigint' then '[bigint]' when 'datetime' then '[datetime]'
  5.                 when 'numeric' then '[numeric]('+convert(nvarchar(10),a.prec)+','+convert(nvarchar(10),a.scale)+')' 
  6.                 else '['+b.name+']('+(case convert(nvarchar(10),a.length) when '-1' then 'max' else convert(nvarchar(10),a.length) end)+')' end ) end) as FieldType, 
  7. (case when a.isnullable=1 then ' 'else 'NOT NULL' end) AllowEmpty, 
  8. isnull(e.text,'') DefaultValue,
  9. isnull(g.[value], ' ') AS FieldDesc
  11. FROM syscolumns a 
  12. left join systypes b on a.xtype=b.xusertype 
  13. inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
  14. left join syscomments e on a.cdefault=e.id 
  15. left join sys.extended_properties g on a.id=g.major_id AND a.name=g.name

image.png

syscolumns-字段信息

image.png

systypes - systypes用法说明

FROM ``syscolumns ``a
left join ``systypes ``b on ``a.xtype``=``b.xusertype
image.png

sysobjects - 存放该数据库内创建的所有对象，如约束、默认值、日志、规则、存储过程等，每个对象在表中占一行

image.png
image.png

syscomments - 包含数据库中每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的项

FROM ``syscolumns ``a
left join syscomments e on ``a.cdefault``=``e.id``
image.png

sys.extended_properties - 扩展属性

image.png