SQL Server 2008获取一个表的字段，类型，长度，是否主键，是否为空，注释等信息

最新推荐文章于 2021-06-07 15:18:22 发布

搬砖狗-小强

最新推荐文章于 2021-06-07 15:18:22 发布

阅读量835

点赞数

分类专栏： SQL 文章标签： SQL Server 2008

SQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

 
  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 
  parent_obj=a.id  
  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 a 
 
 
  left 
   join 
  systypes b  
  on 
  a.xusertype=b.xusertype 
 
 
  inner 
   join 
  sysobjects d  
  on 
  a.id=d.id   
  and 
  d.xtype= 
  'U' 
  and  
  d. 
  name 
  <> 
  'dtproperties' 
 
 
  left 
   join 
  syscomments e  
  on 
  a.cdefault=e.id 
 
 
  left 
   join 
  sys.extended_properties g  
  on 
  a.id=g.major_id  
  and 
  a.colid=g.minor_id  
 
 
  left 
   join 
  sys.extended_properties f  
  on 
  d.id=f.major_id  
  and 
  f.minor_id=0 
 
 
  --where d.name='TableName'    --如果只查询指定表,加上此条件 
 
 
  order 
   by 
  a.id,a.colorder 
 

 
  select 
 
 
    
  c. 
  name 
  as 
  cname,c.prec  
  AS 
  Prec,c.isnullable  
  AS 
  isnullable, 
 
 
    
  [IsPk]= 
  case 
  when 
  exists( 
  SELECT 
  1  
  FROM 
  sysobjects  
  where 
  xtype= 
  'PK' 
  and 
  parent_obj=c.id  
  and 
  name 
   in 
  ( 
 
 
           
  SELECT 
  name 
  FROM 
  sysindexes  
  WHERE 
  indid  
  in 
  ( 
  SELECT 
  indid  
  FROM 
  sysindexkeys  
  WHERE 
  id = c.id  
  AND 
  colid=c.colid))) 
  then 
  '1' 
  else 
   '0' 
  end 
  , 
 
 
  [defaultval]= 
  isnull 
  (e.text, 
  '' 
  ), 
 
 
  t. 
  name 
  as 
  tname ,( 
  select 
  value  
  from 
  sys.extended_properties  
  as 
  ex  
  where 
  ex.major_id = c.id 
  and 
  ex.minor_id = c.colid) 
  as 
  notes  
 
 
  from 
 
 
    
  syscolumns 
  as 
  c  
  inner 
  join 
  sys.tables  
  as 
  ta  
  on 
  c.id=ta.object_id  
 
 
    
  inner 
  join  
  ( 
  select 
  name 
  ,system_type_id  
  from 
  sys.types  
  where 
  name 
  <> 
  'sysname' 
  ) 
  as 
  t  
  on 
  c.xtype=t.system_type_id  
 
 
    
  left 
  join 
  syscomments e  
  on 
   c.cdefault=e.id 
 
 
  where 
 
 
    
  ta. 
  name 
  = 
  'TestTb' 
  order 
  by 
  c.colid 
 