Consulta el contenido de los procedimientos almacenados en SQL Server
Estoy explorando un sistema de base de datos heredado y tengo muy poco conocimiento de sus componentes internos. Me gustaría encontrar todos los procedimientos almacenados que invocan otro procedimiento almacenado A
.
¿Cuál es la mejor manera de hacer esto?
Puedo escribir algo como este pseudocódigo:
select name from AllStoredProcedures as Asp where Asp.TextualContent contains 'A'
Asp.TextualContent
significa el SQL real contenido en el SP.
24
4 answers
SELECT
OBJECT_NAME(OBJECT_ID),
definition
FROM
sys.sql_modules
WHERE
objectproperty(OBJECT_ID, 'IsProcedure') = 1
AND definition LIKE '%Foo%'
45
Author: Martin Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-12-11 13:59:23
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-12-11 13:59:23
Para SQL Server 2005/2008:
SELECT s.name SchemaName
,o.name RoutineName
,o.[type] RoutineType
,procs.*
FROM sys.sql_modules procs
INNER JOIN sys.objects o ON procs.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE procs.[definition] LIKE '%A%'
--AND o.[type] = 'P' --'P' for stored procedures
6
Author: Bogdan Sahlean,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-07 11:23:40
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-07 11:23:40
Esta consulta recuperará la definición textual de los procedimientos almacenados y filtrará usando un comodín simple.
Para 2000 (no probado, pero IIRC es la tabla correcta):
select p.[type]
,p.[name]
,c.[text]
from sysobjects p
join syscomments c
on p.object_id = c.id
where p.[type] = 'P'
and c.[text] like '%foo%'
Para 2005:
select p.[type]
,p.[name]
,c.[text]
from sys.objects p
join sys.syscomments c
on p.object_id = c.id
where p.[type] = 'P'
and c.[text] like '%foo%'
Para 2005 y 2008 +
select p.[type]
,p.[name]
,c.[definition]
from sys.objects p
join sys.sql_modules c
on p.object_id = c.object_id
where p.[type] = 'P'
and c.[definition] like '%foo%'
2
Author: ConcernedOfTunbridgeWells,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-07 11:23:47
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-07 11:23:47
Pruebe esta única instrucción que puede resolver su problema..
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.myStoredProc'))
O
SELECT @objname= OBJECT_DEFINITION(OBJECT_ID(N'dbo.myStoredProc'))
print @objname
1
Author: Apps Tawale,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-02 16:28:20
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-09-02 16:28:20