sql语句查询数据库表结构信息

开发中经常用到查询指定表及其字段的信息，以下是我整理的SQL语句查询方法，供自己平时使用也提供给大家参考！ 1.适用MS SQL SERVER: 1 SELECT 2 表名 = case when a.colorder= 1 then d.name else '' end, 3 表说明 = case when a.colorder= 1 then isnull(

开发中经常用到查询指定表及其字段的信息，以下是我整理的SQL语句查询方法，供自己平时使用也提供给大家参考！

1.适用MS SQL SERVER:

  1   SELECT
   2  表名 =  case  when a.colorder= 1  then d.name  else   ''   end,
   3  表说明 =  case  when a.colorder= 1  then isnull(f.value, '' )  else   ''   end,
   4  字段序号 =  a.colorder,
   5  字段名 =  a.name,
   6  标识 =  case  when COLUMNPROPERTY( a.id,a.name, '  IsIdentity  ' )= 1  then  '  √  '  else   ''   end,
   7  主键 =  case  when exists(SELECT  1  FROM sysobjects  where  xtype= '  PK  '  and parent_obj=a.id and name  in   (
   8  SELECT name FROM sysindexes WHERE indid  in  (
   9  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then  '  √  '   else   ''   end,
  10  类型 =  b.name,
  11  占用字节数 =  a.length,
  12  长度 = COLUMNPROPERTY(a.id,a.name, '  PRECISION  '  ),
  13  小数位数 = isnull(COLUMNPROPERTY(a.id,a.name, '  Scale  ' ), 0  ),
  14  允许空 =  case  when a.isnullable= 1  then  '  √  '  else   ''   end,
  15  默认值 = isnull(e.text, ''  ),
  16  字段说明 = isnull(g.[value], ''  )
  17   FROM
  18   syscolumns a
  19   left join
  20   systypes b
  21   on
  22  a.xusertype= b.xusertype
  23   inner join
  24   sysobjects d
  25   on
  26  a.id=d.id and d.xtype= '  U  '  and d.name  '  dtproperties  ' 
 27   left join
  28   syscomments e
  29   on
  30  a.cdefault= e.id
  31   left join
  32   sys.extended_properties g
  33   on
  34  --a.id=g.id and a.colid= g.smallid
  35  a.id=g.major_id and a.colid= g.Minor_id
  36   left join
  37   sys.extended_properties f
  38   on
  39  --d.id=f.id and f.smallid= 0 
 40  d.id=f.major_id and f.Minor_id= 0 
 41   where 
 42  d.name= '  表名  '  -- 如果只查询指定表,加上此条件
  43   order by
  44  a.id,a.colorder 

2.适用ORACLE:

  1   SELECT
   2  USER_TAB_COLS.TABLE_NAME  as   表名,
   3  user_tab_comments测试数据ments  as   表备注,
   4  USER_TAB_COLS.COLUMN_ID  as   列序号,
   5  user_col_comments测试数据ments  as   列备注,
   6  USER_TAB_COLS.COLUMN_NAME  as   列名 ,
   7  USER_TAB_COLS.DATA_TYPE  as   数据类型,
   8  USER_TAB_COLS.DATA_LENGTH  as   长度,
   9  USER_TAB_COLS.NULLABLE  as   是否为空,
  10  user_cons_columns.constraint_name  as   约束名,
  11  user_constraints.constraint_type  as   主键
  12   FROM USER_TAB_COLS inner join user_col_comments on
  13  user_col_comments.TABLE_NAME= USER_TAB_COLS.TABLE_NAME
  14  and user_col_comments.COLUMN_NAME= USER_TAB_COLS.COLUMN_NAME
  15  INNER join user_cons_columns on user_cons_columns.table_name= USER_TAB_COLS.table_name
  16  INNER join user_constraints on user_constraints.table_name=USER_TAB_COLS.table_name and user_constraints.constraint_name= user_cons_columns.constraint_name
  17  inner join user_tab_comments on USER_TAB_COLS.TABLE_NAME= user_tab_comments.TABLE_NAME
  18  WHERE USER_TAB_COLS.table_name= '  表名  ' 
 19  ORDER BY USER_TAB_COLS.TABLE_NAME 

原文其它网址：http://HdhCmsTestzuowenjun.cn/post/2014/08/28/26.html

查看更多关于sql语句查询数据库表结构信息的详细内容...