查询数据库表和字段sql语句

今天整理资料时看到有这样一个查询数据库中的表和字段信息的语句，很强! 就是忘了当初谁写的了，印像中该是邹建所创。也贴出来以后备用吧。

 
 

  
   1
  
  
  
  SELECT
  
  

  
   2
  
  表名   
  
  =
  
   
  
  CASE
  
   a.colorder 
  
  WHEN
  
   
  
  1
  
   
  
  THEN
  
   c.name 
  
  ELSE
  
   
  
  ''
  
   
  
  END
  
  ,

  
   3
  
  序     
  
  =
  
   a.colorder,

  
   4
  
  字段名 
  
  =
  
   a.name,

  
   5
  
  标识   
  
  =
  
   
  
  CASE
  
   
  
  COLUMNPROPERTY
  
  (a.id,a.name,
  
  '
  
  IsIdentity
  
  '
  
  ) 
  
  WHEN
  
   
  
  1
  
   
  
  THEN
  
   
  
  '
  
  √
  
  '
  
   
  
  ELSE
  
   
  
  ''
  
   
  
  END
  
  ,

  
   6
  
  主键   
  
  =
  
   
  
  CASE
  
  

  
   7
  
  
  
  WHEN
  
   
  
  EXISTS
  
   (

  
   8
  
  
  
  SELECT
  
   
  
  *
  
  

  
   9
  
  
  
  FROM
  
   sysobjects

  
  10
  
  
  
  WHERE
  
   xtype
  
  =
  
  '
  
  PK
  
  '
  
   
  
  AND
  
   name 
  
  IN
  
   (

  
  11
  
  
  
  SELECT
  
   name

  
  12
  
  
  
  FROM
  
   sysindexes

  
  13
  
  
  
  WHERE
  
   id
  
  =
  
  a.id 
  
  AND
  
   indid 
  
  IN
  
   (

  
  14
  
  
  
  SELECT
  
   indid

  
  15
  
  
  
  FROM
  
   sysindexkeys

  
  16
  
  
  
  WHERE
  
   id
  
  =
  
  a.id 
  
  AND
  
   colid 
  
  IN
  
   (

  
  17
  
  
  
  SELECT
  
   colid

  
  18
  
  
  
  FROM
  
   syscolumns

  
  19
  
  
  
  WHERE
  
   id
  
  =
  
  a.id 
  
  AND
  
   name
  
  =
  
  a.name

  
  20
  
  )

  
  21
  
  )

  
  22
  
  )

  
  23
  
  )

  
  24
  
  
  
  THEN
  
   
  
  '
  
  √
  
  '
  
  

  
  25
  
  
  
  ELSE
  
   
  
  ''
  
  

  
  26
  
  
  
  END
  
  ,

  
  27
  
  类型   
  
  =
  
   b.name,

  
  28
  
  字节数 
  
  =
  
   a.length,

  
  29
  
  长度   
  
  =
  
   
  
  COLUMNPROPERTY
  
  (a.id,a.name,
  
  '
  
  Precision
  
  '
  
  ),

  
  30
  
  小数   
  
  =
  
   
  
  CASE
  
   
  
  ISNULL
  
  (
  
  COLUMNPROPERTY
  
  (a.id,a.name,
  
  '
  
  Scale
  
  '
  
  ),
  
  0
  
  )

  
  31
  
  
  
  WHEN
  
   
  
  0
  
   
  
  THEN
  
   
  
  ''
  
  

  
  32
  
  
  
  ELSE
  
   
  
  CAST
  
  (
  
  COLUMNPROPERTY
  
  (a.id,a.name,
  
  '
  
  Scale
  
  '
  
  ) 
  
  AS
  
   
  
  VARCHAR
  
  )

  
  33
  
  
  
  END
  
  ,

  
  34
  
  允许空 
  
  =
  
   
  
  CASE
  
   a.isnullable 
  
  WHEN
  
   
  
  1
  
   
  
  THEN
  
   
  
  '
  
  √
  
  '
  
   
  
  ELSE
  
   
  
  ''
  
   
  
  END
  
  ,

  
  35
  
  默认值 
  
  =
  
   
  
  ISNULL
  
  (d.
  
  [
  
  text
  
  ]
  
  ,
  
  ''
  
  ),

  
  36
  
  说明   
  
  =
  
   
  
  ISNULL
  
  (e.
  
  [
  
  value
  
  ]
  
  ,
  
  ''
  
  )

  
  37
  
  
  
  FROM
  
   syscolumns a

  
  38
  
  
  
  LEFT
  
    
  
  JOIN
  
   systypes      b 
  
  ON
  
   a.xtype
  
  =
  
  b.xusertype

  
  39
  
  
  
  INNER
  
   
  
  JOIN
  
   sysobjects    c 
  
  ON
  
   a.id
  
  =
  
  c.id 
  
  AND
  
   c.xtype
  
  =
  
  '
  
  U
  
  '
  
   
  
  AND
  
   c.name
  
  <>
  
  '
  
  dtproperties
  
  '
  
  

  
  40
  
  
  
  LEFT
  
    
  
  JOIN
  
   syscomments   d 
  
  ON
  
   a.cdefault
  
  =
  
  d.id

  
  41
  
  
  
  LEFT
  
    
  
  JOIN
  
   sysproperties e 
  
  ON
  
   a.id
  
  =
  
  e.id 
  
  AND
  
   a.colid
  
  =
  
  e.smallid

  
  42
  
  
  
  ORDER
  
   
  
  BY
  
   c.name, a.colorder
 
 

我修改一下，变个精简版本的：

1
2 select  a.name, b.xtype,b.name
3 from  syscolumns a
4 inner    JOIN  systypes  b
5 ON  a.xtype = b.xusertype
6 inner   join  sysobjects c  ON
7 a.id = c.id  AND  c.xtype = ' U '   AND  c.name <> ' dtproperties '   where  c.name  =  表名