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的帮助中能够查到明细