¿Cómo encontrar un texto dentro de procedimientos / disparadores de SQL Server?
Tengo un linkedserver que cambiará. Algunos procedimientos llaman al servidor vinculado de la siguiente manera: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE
. Tenemos disparadores también haciendo este tipo de trabajo. Necesitamos encontrar todos los lugares que usan [10.10.100.50]
para cambiarlo.
En SQL Server Management Studio Express, no encontré una característica como "buscar en toda la base de datos" en Visual Studio. ¿Puede un sys-select especial ayudarme a encontrar lo que necesito?
13 answers
Aquí hay una parte de un procedimiento que uso en mi sistema para encontrar texto....
DECLARE @Search varchar(255)
SET @Search='[10.10.100.50]'
SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,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
2011-10-13 03:29:05
[Respuesta tardía pero con suerte útil]
El uso de tablas del sistema no siempre da resultados 100% correctos porque podría haber una posibilidad de que algunos procedimientos almacenados y/o vistas estén cifrados, en cuyo caso necesitará usar la conexión DAC para obtener los datos que necesita.
Recomiendo usar una herramienta de terceros como ApexSQL Search que puede tratar con objetos cifrados fácilmente.
La tabla del sistema Syscomments dará un valor nulo para el texto columna en caso de que el objeto esté cifrado.
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-05-03 11:31:47
Puedes encontrarlo como
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'
Listará distintos nombres de procedimientos almacenados que contienen texto como 'User' dentro del procedimiento almacenado. Más información
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-21 15:36:52
-- Declare the text we want to search for
DECLARE @Text nvarchar(4000);
SET @Text = 'employee';
-- Get the schema name, table name, and table type for:
-- Table names
SELECT
TABLE_SCHEMA AS 'Object Schema'
,TABLE_NAME AS 'Object Name'
,TABLE_TYPE AS 'Object Type'
,'Table Name' AS 'TEXT Location'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'+@Text+'%'
UNION
--Column names
SELECT
TABLE_SCHEMA AS 'Object Schema'
,COLUMN_NAME AS 'Object Name'
,'COLUMN' AS 'Object Type'
,'Column Name' AS 'TEXT Location'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Text+'%'
UNION
-- Function or procedure bodies
SELECT
SPECIFIC_SCHEMA AS 'Object Schema'
,ROUTINE_NAME AS 'Object Name'
,ROUTINE_TYPE AS 'Object Type'
,ROUTINE_DEFINITION AS 'TEXT Location'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%'
AND (ROUTINE_TYPE = 'function' OR 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
2013-03-20 17:15:13
Esto funcionará para ti:
use [ANALYTICS] ---> put your DB name here
GO
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, 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 '%SEARCH_WORD_HERE%' collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type;
GO
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-08-29 19:38:02
Hay mucho mejores soluciones que modificar el texto de sus procedimientos almacenados, funciones y vistas cada vez que cambia el servidor vinculado. Estas son algunas opciones:
Actualice el servidor vinculado. En lugar de usar un servidor vinculado con su dirección IP, cree un nuevo servidor vinculado con el nombre del recurso como
Finance
oDataLinkProd
o algo así. Luego, cuando necesite cambiar a qué servidor se accede, actualice el servidor vinculado para que apunte al nuevo servidor (o soltarlo y recrearlo).Aunque lamentablemente no puede crear sinónimos para los servidores o esquemas vinculados, SÍ PUEDE crear sinónimos para los objetos que se encuentran en los servidores vinculados. Por ejemplo, su procedimiento
[10.10.100.50].dbo.SPROCEDURE_EXAMPLE
podría usar alias. Tal vez crear un esquemadatalinkprod
, entoncesCREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;
. Luego, escriba un procedimiento almacenado que acepte un nombre de servidor vinculado, que consulta todos los objetos potenciales de la base de datos remota y (re)crea sinónimos para ellos. Todos sus SPs y funciones se reescriben solo una vez para usar los nombres sinónimos que comienzan condatalinkprod
, y siempre después de eso, para cambiar de un servidor vinculado a otro, simplemente hagaEXEC dbo.SwitchLinkedServer '[10.10.100.51]';
y en una fracción de segundo está utilizando un servidor vinculado diferente.
Puede haber aún más opciones. Recomiendo encarecidamente usar las técnicas superiores de preprocesamiento, configuración o indirección en lugar de cambiar los scripts escritos por humanos. Actualizar automáticamente los scripts creados por la máquina está bien, esto es preprocesamiento. Haciendo cosas manualmente es horrible.
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-20 18:22:31
select text
from syscomments
where text like '%your text here%'
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-04-30 07:02:25
Uso este para el trabajo. dejar fuera el []'s sin embargo en el campo @TEXT, parece querer devolver todo...
SET NOCOUNT ON DECLARE @TEXT VARCHAR(250) DECLARE @SQL VARCHAR(250) SELECT @TEXT='10.10.100.50' CREATE TABLE #results (db VARCHAR(64), objectname VARCHAR(100),xtype VARCHAR(10), definition TEXT) SELECT @TEXT as 'Search String' DECLARE #databases CURSOR FOR SELECT NAME FROM master..sysdatabases where dbid>4 DECLARE @c_dbname varchar(64) OPEN #databases FETCH #databases INTO @c_dbname WHILE @@FETCH_STATUS -1 BEGIN SELECT @SQL = 'INSERT INTO #results ' SELECT @SQL = @SQL + 'SELECT ''' + @c_dbname + ''' AS db, o.name,o.xtype,m.definition ' SELECT @SQL = @SQL + ' FROM '+@c_dbname+'.sys.sql_modules m ' SELECT @SQL = @SQL + ' INNER JOIN '+@c_dbname+'..sysobjects o ON m.object_id=o.id' SELECT @SQL = @SQL + ' WHERE [definition] LIKE ''%'+@TEXT+'%''' EXEC(@SQL) FETCH #databases INTO @c_dbname END CLOSE #databases DEALLOCATE #databases SELECT * FROM #results order by db, xtype, objectname DROP TABLE #results
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
2009-03-23 19:34:13
He usado estos en el pasado:
- Buscar en todos los procedimientos almacenados por el usuario un nombre de tabla
- Buscar y reemplazar datos de SQL Server en todas las columnas de todas las tablas
En este caso particular, donde necesita reemplazar una cadena específica a través de procedimientos almacenados, el primer enlace es probablemente más relevante.
Un poco fuera de tema, el complemento Quick Find también es útil para buscar nombres de objetos con SQL Server Management Studio. Hay una versión modificadadisponible con algunas mejoras, y otra versión más nueva también disponible en Codeplex con algunos otros complementos útiles también.
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
2009-03-23 19:46:06
Cualquier búsqueda con la instrucción select solo produce el nombre del objeto, donde la palabra clave de búsqueda contiene. La forma más fácil y eficiente es obtener el script de procedimiento/función y luego buscar en el archivo de texto generado, también sigo esta técnica :) Por lo que son precisos.
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-04 12:17:43
Este lo probé en SQL2008, que puede buscar desde toda la base de datos de una sola vez.
Create table #temp1
(ServerName varchar(64), dbname varchar(64)
,spName varchar(128),ObjectType varchar(32), SearchString varchar(64))
Declare @dbid smallint, @dbname varchar(64), @longstr varchar(5000)
Declare @searhString VARCHAR(250)
set @searhString='firstweek'
declare db_cursor cursor for
select dbid, [name]
from master..sysdatabases
where [name] not in ('master', 'model', 'msdb', 'tempdb', 'northwind', 'pubs')
open db_cursor
fetch next from db_cursor into @dbid, @dbname
while (@@fetch_status = 0)
begin
PRINT 'DB='+@dbname
set @longstr = 'Use ' + @dbname + char(13) +
'insert into #temp1 ' + char(13) +
'SELECT @@ServerName, ''' + @dbname + ''', Name
, case when [Type]= ''P'' Then ''Procedure''
when[Type]= ''V'' Then ''View''
when [Type]= ''TF'' Then ''Table-Valued Function''
when [Type]= ''FN'' Then ''Function''
when [Type]= ''TR'' Then ''Trigger''
else [Type]/*''Others''*/
end
, '''+ @searhString +''' FROM [SYS].[SYSCOMMEnTS]
JOIN [SYS].objects ON ID = object_id
WHERE TEXT LIKE ''%' + @searhString + '%'''
exec (@longstr)
fetch next from db_cursor into @dbid, @dbname
end
close db_cursor
deallocate db_cursor
select * from #temp1
Drop table #temp1
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-05-05 08:29:01
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your Text%'
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-06 19:45:25
Puede buscar dentro de las definiciones de todos los objetos de la base de datos utilizando el siguiente SQL:
SELECT
o.name,
o.id,
c.text,
o.type
FROM
sysobjects o
RIGHT JOIN syscomments c
ON o.id = c.id
WHERE
c.text like '%text_to_find%'
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-03-22 10:07:51