SQL 查询业务库_SQL 查询数据字典_sql查询表结构，过程，视图，主键，外键，约束

SQL 查询业务库

--  查询非系统数据库

Select name FROM Master.. SysDatabases where dbid>4

--  选择water数据库下的所有表
use   [ water ]   SELECT  name  FROM  sysobjects  WHERE  xtype  =   ' U '   Or  xtype  =   ' S '

--  选择water数据库下的所有用户表
use   [ water ]   SELECT  name  FROM  sysobjects  WHERE  xtype  =   ' U '   AND   OBJECTPROPERTY  (id,  ' IsMSShipped ' )  =   0

--  查询water数据库下的admin表的字段名,长度,类型,字段说明
use   [ water ]   SELECT  a. [ name ]   as   ' 字段名 ' ,a.length  ' 长度 ' ,c. [ name ]   ' 类型 ' ,e.value  as   ' 字段说明 '   FROM  syscolumns  a 
left     join     systypes    b    on       a.xusertype = b.xusertype 
left       join      systypes     c      on       a.xtype  =  c.xusertype 
inner     join    sysobjects  d    on       a.id = d.id      and    d.xtype = ' U '  
left   join  sys.extended_properties e  on  a.id  =  e.major_id  and  a.colid  =  e.minor_id  and  e.name = ' MS_Description '
where  d.name = ' admin '

SQL 查询数据字典

select name from sysobjects where xtype='TR' --所有触发器
select name from sysobjects where xtype='P' --所有存储过程
select name from sysobjects where xtype='V' --所有视图
select name from sysobjects where xtype='U' --所有表

以上为SqlServer用法

Select object_name From user_objects Where object_type='TRIGGER'; --所有触发器
Select object_name From user_objects Where object_type='PROCEDURE'; --所有存储过程
Select object_name From user_objects Where object_type='VIEW'; --所有视图
Select object_name From user_objects Where object_type='TABLE'; --所有表

以上为Oracle用法

 

sql查询表结构，过程，视图，主键，外键，约束

 

一、表结构查询

SELECT TOP (100) PERCENT a.name AS zdm,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS bs ,
CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
 AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
 INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid)
 THEN '1' ELSE '0' END AS zj , b.name AS lx, a.length AS cd, COLUMNPROPERTY(a.id, a.name,'PRECISION')
 AS jd, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS xsws,a.isnullable AS yxk, ISNULL(e.text, '')
  AS mrz, ISNULL(g.value, '') AS zdsm FROM dbo.syscolumns AS a LEFT OUTER JOIN dbo.systypes AS b ON a.xtype = b.xusertype
  INNER JOIN dbo.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN
   dbo.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties AS g
   ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN sys.extended_properties
   AS f ON d.id = f.major_id AND f.minor_id = 0  where d .name='查询的表名'

二、
-- 查询存储过程
select CASE a.xtype  WHEN 'p' THEN '存储过程' end as lx ,a.name, b.text from sysobjects a left outer join syscomments b on a.id = b.id where xtype='p'
--查询视图
select  CASE a.xtype  WHEN 'v' THEN '视图' end as lx,a.name , b.text from sysobjects a left outer join syscomments b on a.id = b.id where xtype='v'

--主键，外键，约束
select
CASE a.xtype  WHEN 'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束'
END AS lx,a.name AS name,
b.text from sysobjects a left outer join syscomments b on a.id = b.id
where (a.xtype IN ( 'C',  'F','PK')) AND
(OBJECTPROPERTY(a.id, N'IsMSShipped') = 0) and a.parent_obj=(select id from sysobjects where name = 'table_2')

环境是用的sql2008

其中涉及到的表  与视图  过程的名称在sql的帮助中能够查到明细