Comment trouver un texte dans les Procédures / déclencheurs SQL Server?
J'ai un linkedserver qui va changer. Certaines procédures appellent le serveur lié comme ceci: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE
. Nous avons des déclencheurs qui font aussi ce genre de travail. Nous devons trouver tous les endroits qui utilisent [10.10.100.50]
pour le changer.
Dans SQL Server Management Studio Express, Je n'ai pas trouvé une fonctionnalité comme "Rechercher dans toute la base de données" dans Visual Studio. Peut spéciale sys-sélectionner m'aider à trouver ce dont j'ai besoin?
13 réponses
Voici une partie d'une procédure que j'utilise sur mon système pour trouver du texte....
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
[réponse Tardive mais j'espère utile]
L'utilisation de tables système ne donne pas toujours des résultats corrects à 100% car il est possible que certaines procédures et/ou vues stockées soient cryptées, auquel cas vous devrez utiliser la connexion DAC pour obtenir les données dont vous avez besoin.
Je recommande d'utiliser un outil tiers tel que ApexSQL Search qui peut traiter facilement les objets cryptés.
La table système Syscomments donnera une valeur null pour le texte colonne dans le cas où l'objet est chiffré.
Vous pouvez le trouver comme
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'
Il listera les noms de procédure stockés distincts qui contiennent du texte comme "utilisateur" dans la procédure stockée. plus d'Informations
-- 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');
Cela fonctionnera pour vous:
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
Il y a beaucoup {[9] } de meilleures solutions que de modifier le texte de vos procédures, fonctions et vues stockées chaque fois que le serveur lié change. Voici quelques options:
Mettez à jour le serveur lié. Au lieu d'utiliser un serveur lié nommé avec son adresse IP, créez un nouveau serveur lié avec le nom de la ressource telle que
Finance
ouDataLinkProd
ou une autre. Ensuite, lorsque vous devez modifier le serveur atteint, mettez à jour le serveur lié pour pointer vers le nouveau serveur (ou déposez-le et recréez-le).Bien que vous ne puissiez malheureusement pas créer de synonymes pour des serveurs ou des schémas liés, Vous pouvez créer des synonymes pour des objets situés sur des serveurs liés. Par exemple, votre procédure
[10.10.100.50].dbo.SPROCEDURE_EXAMPLE
pourrait par aliased. Peut-être créer un schémadatalinkprod
, puisCREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;
. Ensuite, écrivez une procédure stockée qui accepte un nom de serveur lié, qui interroge tous les objets potentiels de la base de données distante et (re) crée des synonymes pour eux. Tous vos SPs et fonctions sont réécrits juste une fois pour utiliser les noms de synonymes commençant pardatalinkprod
, et toujours après cela, pour passer d'un serveur lié à un autre, vous faites justeEXEC dbo.SwitchLinkedServer '[10.10.100.51]';
et en une fraction de seconde, vous utilisez un serveur lié différent.
Il peut y avoir encore plus d'options. Je recommande fortement d'utiliser les techniques supérieures de prétraitement, de configuration ou d'indirection plutôt que de changer les scripts écrits par l'homme. La mise à jour automatique des scripts créés par la machine est correcte, c'est un prétraitement. Faire les choses manuellement est horrible.
select text
from syscomments
where text like '%your text here%'
J'utilise celui-ci pour le travail. laissez les [] dans le champ @ TEXT, semble vouloir tout retourner...
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
Je les ai utilisés dans le passé:
- Recherche d'un nom de table dans toutes les procédures stockées par l'utilisateur
- rechercher et remplacer les données SQL Server dans toutes les colonnes de toutes les tables
Dans ce cas particulier, où vous devez remplacer une chaîne spécifique entre les procédures stockées, le premier lien est probablement plus pertinent.
Un peu hors sujet, le Quick Find add-in est également utile pour la recherche de noms d'objets avec SQL Server Management Studio. Il y a une version modifiée disponible avec quelques améliorations, et une autre version plus récente également disponible sur Codeplex avec d'autres compléments utiles.
Toute recherche avec instruction select ne vous donne que le nom de l'objet, où search keyword contient. Le moyen le plus simple et efficace est d'obtenir un script de procédure/fonction, puis de rechercher dans le fichier texte généré, Je suit également cette technique :) donc vous êtes exact.
Celui - ci j'ai essayé dans SQL2008, qui peut rechercher à partir de toute la base de données en une seule fois.
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
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Your Text%'
Vous pouvez rechercher dans les définitions de tous les objets de base de données en utilisant le SQL suivant:
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%'