MySQL、Oracle、SQL server 获取存储过程、触发器SQL

SQL

1. MySQL

1.1 视图

-- DataBaseName 为要查询的数据库名称
SHOW FULL TABLES FROM DataBaseName where `Table_type`='VIEW';

1.2 存储过程

-- DataBaseName为要查询的数据库名称

select
	*
from
	information_schema.ROUTINES
where
	ROUTINE_SCHEMA = 'DataBaseName'
	and ROUTINE_TYPE in ('PROCEDURE',
	'FUNCTION')
order by
	ROUTINE_NAME
	
-- 存储过程创建语句

SHOW CREATE PROCEDURE oa9test.`ChangeDataToMain`
	

1.3 触发器

-- DataBaseName为要查询的数据库名称

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'DataBaseName'

-- 触发器定义语句

SHOW CREATE TRIGGER oa9test.`FRAME_OU_TRIGGER_INSERT`

2. Oracle

2.1 视图

-- DataBaseName为要查询的数据库名称

SELECT
	o.OWNER,
	o.OBJECT_NAME AS VIEW_NAME,
	'VIEW' AS OBJECT_TYPE,
	o.STATUS,
	NULL,
	NULL,
	NULL,
	'NO',
	NULL,
	NULL,
	o.TEMPORARY,
	o.SECONDARY,
	'NO',
	0
FROM
	ALL_OBJECTS o
WHERE
	o.OWNER = 'DataBaseName'
	AND o.OBJECT_TYPE = 'VIEW'
	
-- 查询视图定义语句
-- OWNER、VIEW_NAME替换为对应的数据

SELECT TEXT,TYPE_TEXT,OID_TEXT,VIEW_TYPE_OWNER,VIEW_TYPE,SUPERVIEW_NAME
FROM ALL_VIEWS WHERE OWNER='EPOINTF9_5' AND VIEW_NAME='VIEW_APP_INFOCLASS'
	

2.2 存储过程

-- DataBaseName为要查询的数据库名称

SELECT
	*
FROM
	ALL_OBJECTS
WHERE
	OBJECT_TYPE IN ('PROCEDURE','FUNCTION')
	AND OWNER = 'DataBaseName'
ORDER BY
	OBJECT_NAME
	
-- 存储过程查询SQL，下面语句的第一个type即为上面SQL查询出来的字段 OBJECT_TYPE的值，其余参数按照实际情况填写

SELECT TEXT FROM DBA_SOURCE WHERE TYPE=? AND OWNER=? AND NAME=? ORDER BY LINE

2.3 触发器

-- DataBaseName为要查询的数据库名称
-- 模式触发器

SELECT
	*
FROM
	ALL_TRIGGERS
WHERE
	OWNER ='DataBaseName'
	AND TRIM(BASE_OBJECT_TYPE) IN ('DATABASE','SCHEMA')
ORDER BY
	TRIGGER_NAME
	
-- 	

-- 表触发器

SELECT
	*
FROM
	ALL_TRIGGERS
WHERE
	TABLE_OWNER = 'EPOINT9_9_4_1'
	AND TABLE_NAME = 'API_CHANNEL_TARGET'
ORDER BY
	TRIGGER_NAME
	
	
-- 表触发器创建语句

SELECT
	TEXT
FROM
	DBA_SOURCE
WHERE
	TYPE ='TRIGGER' 
	AND OWNER ='EPOINT9_9_4_1' //模式名
	AND NAME ='TG_EPOINTSFORM' // 触发器名
ORDER BY
	LINE
	

3. SQLServer

3.1 视图

-- DWDiagnostics 修改为对应的模式名

SELECT
	o.*,
	ep.value as description
FROM
	DWDiagnostics.sys.all_objects o
LEFT OUTER JOIN DWDiagnostics.sys.extended_properties ep ON
	ep.class = 1
	AND ep.major_id = o.object_id
	AND ep.minor_id = 0
	AND ep.name = 'MS_Description'
WHERE
	o.type = 'V'
	AND o.schema_id = 1
	
-- 视图创建语句
-- EpointBid_TP7_LNS为库名称、VIEW_ALLCAIGOUDW 为视图名
EpointBid_TP7_LNS.sys.sp_helptext 'dbo.VIEW_ALLCAIGOUDW'
	

3.2 存储过程

-- DWDiagnostics 修改为对应的模式名
-- ID 可以双击dbo 模块查询到
SELECT p.*,ep.value as description
FROM DWDiagnostics.sys.all_objects p
LEFT OUTER JOIN DWDiagnostics.sys.extended_properties ep ON ep.class=1 AND ep.major_id=p.object_id AND ep.minor_id=0 AND ep.name='MS_Description'
WHERE p.type IN ('P','PC','X','TF','FN','IF') AND p.schema_id=1
ORDER BY p.name

-- 存储过程创建语句
--  EpointBid_TP7_LNS 为数据库名，automarkcount 为存储过程名称

EpointBid_TP7_LNS.sys.sp_helptext 'dbo.automarkcount'

3.3 触发器

-- DWDiagnostics 修改为对应的模式名
-- ID 可以双击dbo 模块查询到

SELECT t.* FROM 
DWConfiguration.sys.triggers t,DWConfiguration.sys.all_objects o
WHERE o.object_id=t.object_id AND o.schema_id=1
ORDER BY t.name

-- 触发器创建语句
-- EpointBid_TP7_LNS 为数据库名 AuteoSetOrderNum 为触发器名称

EpointBid_TP7_LNS.sys.sp_helptext 'dbo.AuteoSetOrderNum'