My OperaBuscar texto en todos los Stored Procedures de una base de datos
Friday, March 16, 2012 4:06:11 PM
Adjunto este script que sirve para buscar un texto específico dentro del contenido de todos los stored procedures de una base de datos. Los únicos parámetros a ingresar para comenzar la búsqueda son: 'DBData' que es el nombre de la base de datos donde queremos buscar y @SearchText = 'Tabla1' donde 'Tabla1' es el texto que queremos encontrar.
Saludos!
--La base de datos en la que se buscará
USE DBData
GO
--Variable que contentendrá el texto a buscar, nombre de una tabla, columna, etc...
DECLARE @SearchText AS VARCHAR(100)
SET @SearchText = 'Tabla1'
--Consulta de los procedimeintos que contienen dicho texto
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
WHEN OBJECTPROPERTY(c.id, 'IsView') = 1
THEN 'View'
END AS 'Object type'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%'+@SearchText+'%' AND
encrypted = 0 AND
( OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsView') = 1 )
ORDER BY [Object type], 'Object name'
fuente
Saludos!
--La base de datos en la que se buscará
USE DBData
GO
--Variable que contentendrá el texto a buscar, nombre de una tabla, columna, etc...
DECLARE @SearchText AS VARCHAR(100)
SET @SearchText = 'Tabla1'
--Consulta de los procedimeintos que contienen dicho texto
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
WHEN OBJECTPROPERTY(c.id, 'IsView') = 1
THEN 'View'
END AS 'Object type'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%'+@SearchText+'%' AND
encrypted = 0 AND
( OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsView') = 1 )
ORDER BY [Object type], 'Object name'
fuente
, luego entramos a la e-Shop en donde nos indicará que cambiamos el lugar de residencia, aceptamos el contrato y finalmente lograremos entrar al contenido, se nos pedirá introducir el código postal (yo puse el 33133 de Florida) y presto! podremos descargar todo lo que queramos.
