¿Cómo encuentro que contiene un procedimiento almacenado?
Necesito buscar un SQL server 2008 para los procedimientos almacenados que contienen donde tal vez el nombre de un campo de base de datos o nombre de variable.
20 answers
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Foo%'
AND ROUTINE_TYPE='PROCEDURE'
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Foo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%Foo%'
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
2012-05-16 05:15:16
Coge una copia de la gratis Red-Gate SQL Search herramienta y empezar a disfrutar de la búsqueda en SQL Server! :-)
Es una herramienta genial y muy útil, y ¡SÍ! es totalmente, absolutamente GRATIS para cualquier tipo de uso.
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-02-22 15:42:36
También puede probar ApexSQL Search - plug-in SSMS gratuito de ApexSQL.
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
2013-02-28 10:19:49
Tomé la respuesta de Kashif y los uní a todos juntos. Extrañamente, a veces, encontré resultados en una de las selecciones pero no en la otra. Así que para estar seguro, corro los 3 cuando estoy buscando algo. Espero que esto ayude:
DECLARE @SearchText varchar(1000) = 'mytext';
SELECT DISTINCT SPName
FROM (
(SELECT ROUTINE_NAME SPName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'
AND ROUTINE_TYPE='PROCEDURE')
UNION ALL
(SELECT OBJECT_NAME(id) SPName
FROM SYSCOMMENTS
WHERE [text] LIKE '%' + @SearchText + '%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id))
UNION ALL
(SELECT OBJECT_NAME(object_id) SPName
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%' + @SearchText + '%')
) AS T
ORDER BY T.SPName
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-10-20 13:37:52
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%FieldName%'
AND ROUTINE_TYPE='PROCEDURE'
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-02-22 14:37:51
En caso de que necesite esquema también:
SELECT DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text]
FROM syscomments AS c
INNER JOIN sys.objects AS o ON c.id = o.[object_id]
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE text LIKE '%foo%'
ORDER BY SCHEMA_NAME(o.schema_id),o.name
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
2012-11-15 00:33:54
Probé el ejemplo anterior, pero no mostraba más de 4000 caracteres, luego lo modificé un poco y pude obtener toda la definición del procedimiento almacenado. Por favor, consulte el script actualizado a continuación -
SELECT SCHEMA_NAME(O.SCHEMA_ID) [SCHEMA_NAME], O.NAME, OBJECT_DEFINITION(OBJECT_ID) TEXT
FROM SYSCOMMENTS AS C
INNER JOIN SYS.OBJECTS AS O ON C.ID = O.[OBJECT_ID]
INNER JOIN SYS.SCHEMAS AS S ON O.SCHEMA_ID = S.SCHEMA_ID
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%FOO%'
ORDER BY SCHEMA_NAME(O.SCHEMA_ID), O.NAME
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
2015-08-18 17:34:43
Primero asegúrese de que está ejecutando la consulta bajo sus credenciales de usuario, y también en el contexto correcto de la base de datos.
USE YOUR_DATABASE_NAME;
De lo contrario, sys.los procedimientos no devuelven nada. Ahora ejecute la consulta de la siguiente manera:
select * from sys.procedures p
join sys.syscomments s on p.object_id = s.id
where text like '%YOUR_TEXT%';
Otra opción es usar INFORMATION_SCHEMA.RUTINA.ROUTINE_DEFINITION, pero tenga en cuenta que solo contiene un número limitado de caracteres (es decir, los primeros 4000 caracteres) de la rutina.
select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YOUR_TEXT%';
Probé en Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
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-05-11 11:44:00
Para cualquier servidor SQL más nuevo que SQL server 2000:
SELECT object_name = OBJECT_NAME(sm.object_id), o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition like '%searchString%'
ORDER BY o.type, o.name, o.object_id
Si alguien está atascado con SQL server 2000, la tabla sql_modules no existe, por lo que usaría syscomments, obtendrá varios registros para procedimientos almacenados de más de 4000 caracteres, pero tendrán el mismo campo c. number para que pueda agrupar las partes juntas para obtener el texto completo del procedimiento almacenado:
Select o.id, c.number, o.name, c.text
from syscomments c
inner join sysobjects o on o.id = c.id
where c.encrypted = 0 and o.type = 'P'
and c.id in
(Select id from syscomments where text like '%searchtext%')
order by objecttype, o.name, o.id, c.number, c.colid
También puedes probar este programa de utilidad que escribí, es más eficiente usar un herramienta rápida que para escribir una consulta SQL. También buscará sus archivos de código fuente.
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
2018-04-08 15:37:48
select * from sys.system_objects
where name like '%cdc%'
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-03-18 05:02:02
create Procedure [dbo].[TextFinder]
(@Text varchar(500),@Type varchar(2)=NULL)
AS
BEGIN
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'`enter code here`
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%' + @Text + '%' and
o.xtype = case when @Type IS NULL then o.xtype else @Type end
ORDER BY Location, ObjectName
END
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
2015-09-18 05:38:33
Cómo Encontrar un Procedimiento Almacenado Que Contenga Texto o Cadena
Muchas veces necesitamos encontrar el texto o cadena en el procedimiento almacenado. Aquí está la consulta para encontrar el texto que contiene.
SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE '%Text%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
Para obtener más información, consulte la URL dada a continuación.
Http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string
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
2017-08-23 10:27:35
SELECT s.name + '.' + o.name ProcedureName
, c.text ProcedureSteps
FROM sys.syscomments c
INNER JOIN
sys.objects o
ON
c.id = o.object_id
INNER JOIN
sys.schemas s
ON
o.schema_id = s.schema_id
WHERE o.type = 'P'
AND c.text LIKE N'%XXXX%'
ORDER BY s.name + '.' + o.name
, c.colid
Esta consulta devuelve el nombre y el contenido de cualquier procedimiento almacenado donde se haga referencia a "XXXX" dentro del procedimiento almacenado.
Esto es útil cuando se encuentran procedimientos que hacen referencia a una tabla / vista / procedimiento específico
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
2013-03-04 17:01:36
SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'
Esto es lo que he escrito en SQL 2012 para seleccionar todos los procedimientos almacenados donde nombre como "CompanySpecialisation"
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-05-27 07:22:56
SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'
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
2015-07-31 06:55:38
Procedimiento almacenado para buscar texto en SP.. {Dinesh Baskaran} Trendy Global Systems pvt ltd
create Procedure [dbo].[TextFinder]
(@Text varchar(500),@Type varchar(2)=NULL)
AS
BEGIN
SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint '
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%' + @Text + '%' and
o.xtype = case when @Type IS NULL then o.xtype else @Type end
ORDER BY Location, ObjectName
END
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
2015-12-31 08:50:20
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%Name%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
Prueba Esto .....
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-01-13 12:28:18
sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''
Esto buscará en todos los procedimientos almacenados de todas las bases de datos. Esto también funcionará para procedimientos largos.
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-05-16 12:28:37
Utilizo este script. Si cambia sus comentarios XML para que se muestren como texto negro sobre un fondo amarillo, obtendrá el efecto de resaltar el texto que está buscando en la columna xml de los resultados. (Herramientas - > Opciones - > Entorno - > Fuentes y colores [Mostrar elementos: Comentario XML]
---------------------------------------------
-------------- Start FINDTEXT ----------
---------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
GO
DECLARE @SearchString VARCHAR(MAX)
SET @SearchString = 'the text you''re looking for'
DECLARE @OverrideSearchStringWith VARCHAR(MAX)
--#############################################################################
-- Use Escape chars in Brackets [] like [%] to find percent char.
--#############################################################################
DECLARE @ReturnLen INT
SET @ReturnLen = 50;
with lastrun
as (select DEPS.OBJECT_ID
,MAX(last_execution_time) as LastRun
from sys.dm_exec_procedure_stats DEPS
group by deps.object_id
)
SELECT OL.Type
,OBJECT_NAME(OL.Obj_ID) AS 'Name'
,LTRIM(RTRIM(REPLACE(SUBSTRING(REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), ''), CHARINDEX(@SearchString, OBJECT_DEFINITION(OL.Obj_ID)) - @ReturnLen, @ReturnLen * 2), @SearchString, ' ***-->>' + @SearchString + '<<--*** '))) AS SourceLine
,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX), REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')), '&', '(A M P)'), '<', '(L T)'), '>', '(G T)'), @SearchString, '<!-->' + @SearchString + '<-->') AS XML) AS 'Hilight Search'
,(SELECT [processing-instruction(A)] = REPLACE(OBJECT_DEFINITION(OL.Obj_ID), NCHAR(0x001F), '')
FOR
XML PATH('')
,TYPE
) AS 'code'
,Modded AS Modified
,LastRun as LastRun
FROM (SELECT CASE P.type
WHEN 'P' THEN 'Proc'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trig'
ELSE 'Func'
END AS 'Type'
,P.OBJECT_ID AS OBJ_id
,P.modify_Date AS modded
,LastRun.LastRun
FROM sys.Objects P WITH (NOLOCK)
LEFT join lastrun on P.object_id = lastrun.object_id
WHERE OBJECT_DEFINITION(p.OBJECT_ID) LIKE '%' + @SearchString + '%'
AND type IN ('P', 'V', 'TR', 'FN', 'IF', 'TF')
-- AND lastrun.LastRun IS NOT null
) OL
OPTION (FAST 10)
---------------------------------------------
---------------- END -----------------
---------------------------------------------
---------------------------------------------
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-10-11 13:41:16
Aquí está la versión de unión que usa Losbear made, pero modificada para que también muestre el nombre del esquema:
DECLARE @SearchText varchar(1000) = 'searchtext';
SELECT DISTINCT sc.SPECIFIC_SCHEMA as 'Schema', sp.SPName as 'Name'
FROM (
(SELECT ROUTINE_NAME SPName
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'
AND ROUTINE_TYPE='PROCEDURE')
UNION ALL
(SELECT OBJECT_NAME(id) SPName
FROM SYSCOMMENTS
WHERE [text] LIKE '%' + @SearchText + '%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id))
UNION ALL
(SELECT OBJECT_NAME(object_id) SPName
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%' + @SearchText + '%')
) AS sp
CROSS APPLY (select SPECIFIC_SCHEMA from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_NAME = sp.SPName) sc
ORDER BY 1
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
2017-07-27 09:33:07