Zoek tekst in opgeslagen procedure in SQL Server

Ik wil een tekst doorzoeken uit al mijn in de database opgeslagen procedures. Ik gebruik de onderstaande SQL:

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 '%[ABD]%';

Ik wil zoeken naar [ABD] in alle opgeslagen procedures, inclusief vierkante haken, maar het geeft niet het juiste resultaat. Hoe kan ik mijn zoekopdracht wijzigen om dit te bereiken?

Antwoord 1, autoriteit 100%

Ontsnap aan de vierkante haken:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Dan worden de vierkante haken behandeld als letterlijke tekenreeksen en niet als jokertekens.

Antwoord 2, autoriteit 59%

Probeer dit verzoek:

Query

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%strHell%'

Antwoord 3, autoriteit 9%

Heb je geprobeerd een van de tools van derden te gebruiken om de zoekopdracht uit te voeren? Er zijn er verschillende die gratis zijn en die me in het verleden veel tijd hebben bespaard.

Hieronder staan ​​twee SSMS-add-ins die ik met veel succes heb gebruikt.

ApexSQL Search Zoekt zowel schema als gegevens in databases en heeft extra functies zoals het bijhouden van afhankelijkheden en meer

SSMS Tools-pakket Heeft dezelfde zoekfunctionaliteit als de vorige en verschillende andere coole functies. Niet gratis voor SQL Server 2012, maar toch zeer betaalbaar.

Ik weet dat dit antwoord niet 100% gerelateerd is aan de vragen (die specifieker waren), maar hopelijk zullen anderen dit nuttig vinden.

Antwoord 4, autoriteit 6%

Ik voer meestal het volgende uit om dat te bereiken:

select distinct object_name(id) 
from syscomments 
where text like '%[ABD]%'
order by object_name(id) 

Antwoord 5, autoriteit 6%

Goede gewoonte om met SQL Server te werken.

Maak onderstaande opgeslagen procedure aan en stel sneltoets in,

CREATE PROCEDURE [dbo].[Searchinall]       
(@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
        FROM   sys.procedures
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'[email protected]+'%'
    END 
    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) View_Name
              ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'[email protected]+'%'
    END 
    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'[email protected]+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END
    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN
        SELECT t.name      AS Table_Name
              ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
               INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
        WHERE  c.name LIKE '%'[email protected]+'%'
        ORDER BY
               Table_Name
    END
END

Nu – Stel de sneltoets in zoals hieronder,

Dus de volgende keer dat je een bepaalde tekst wilt vinden in een van de vier objecten zoals Store procedure, Views, Functions en Tables. U hoeft alleen dat trefwoord in te voeren en op de sneltoets te drukken.

Bijvoorbeeld: ik wil zoeken naar ‘Betalingstabel’ en vervolgens ‘Betalingstabel’ schrijven en ervoor zorgen dat u het geschreven trefwoord selecteert of markeert in de query-editor en op de sneltoets ctrl+4 – het geeft je het volledige resultaat.

Antwoord 6, autoriteit 4%

Redgate’s SQL Search is een geweldig hulpmiddel om dit is een gratis plug-in voor SSMS.

Antwoord 7, autoriteit 3%

Beschouw dit als een “vuil” alternatief maar dit heeft me vele malen gered, vooral wanneer ik niet bekend was met het DB-project. Soms probeer je binnen alle SP’s naar een string te zoeken en vergeet je dat een deel van de gerelateerde logica misschien verborgen was tussen Functies en Triggers of dat het gewoon anders geformuleerd kan worden dan je dacht.

Vanuit uw MSSMS kunt u met de rechtermuisknop op uw DB klikken en selecteren
Tasks -> Generate Scripts om alle SP’s, Fns en Triggers in één .sql-bestand uit te voeren.

Zorg ervoor dat u ook Triggers selecteert!

Gebruik dan gewoon Sublime of Notepad om te zoeken naar de string die je moet vinden. Ik weet dat dit een nogal inefficiënte en paranoïde benadering is, maar het werkt 🙂

Antwoord 8, autoriteit 2%

Je kunt deze ook gebruiken:

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION like '%Search_String%'

Antwoord 9

select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'

Antwoord 10

Het kan je helpen!

SELECT DISTINCT 
      A.NAME AS OBJECT_NAME,
      A.TYPE_DESC
      FROM SYS.SQL_MODULES M 
      INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID
      WHERE M.DEFINITION LIKE '%['[email protected]_TEXT+']%'
      ORDER BY TYPE_DESC

Antwoord 11

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 '%[String]%';

Antwoord 12

U kunt ook gebruiken:

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%flags.%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

Dat zijn inclusief opmerkingen

Antwoord 13

 SELECT DISTINCT OBJECT_NAME([id]),[text] 
 FROM syscomments   
 WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype IN 
 ('TF','FN','V','P') AND status >= 0) AND  
 ([text] LIKE '%text to be search%' ) 

OBJECT_NAME([id]) –> Objectnaam (weergave, winkelprocedure, scalaire functie, tabelfunctienaam)

id (int) = Objectidentificatienummer

xtype char(2) Objecttype. Kan een van de volgende objecttypen zijn:

FN = scalaire functie

P = Opgeslagen procedure

V = Bekijken

TF = Tabelfunctie

Antwoord 14

Ik heb een procedure gemaakt om tekst te zoeken in procedures/functies, tabellen, views of jobs. De eerste parameter @search is het zoekcriterium, @target het zoekdoel, d.w.z. procedures, tabellen, enz. Indien niet gespecificeerd, doorzoek alles. @db is om de database op te geven die moet worden doorzocht, standaard ingesteld op uw huidige database. Hier is mijn vraag in dynamische SQL.

ALTER PROCEDURE [dbo].[usp_find_objects]
(
    @search VARCHAR(255),
    @target VARCHAR(255) = NULL,
    @db VARCHAR(35) = NULL
)
AS
SET NOCOUNT ON;
DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(50)
IF @db <> '' SET @USEDB = 'USE ' + @db
ELSE SET @USEDB = ''
IF @target IS NULL SET @target = ''
SET @TSQL = @USEDB + '
DECLARE @search VARCHAR(128) 
DECLARE @target VARCHAR(128)
SET @search = ''%' + @search + '%''
SET @target = ''' + @target + '''
IF @target LIKE ''%Procedure%'' BEGIN
    SELECT o.name As ''Stored Procedures''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype IN (''P'',''FN'')
    GROUP BY o.name
    ORDER BY o.name
END
ELSE IF @target LIKE ''%View%'' BEGIN
    SELECT o.name As ''Views''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''V''
    GROUP BY o.name
    ORDER BY o.name
END
/* Table - search table name only, need to add column name */
ELSE IF @target LIKE ''%Table%'' BEGIN
    SELECT t.name AS ''TableName''
    FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @search
    ORDER BY TableName
END
ELSE IF @target LIKE ''%Job%'' BEGIN
    SELECT  j.job_id,
        s.srvname,
        j.name,
        js.step_id,
        js.command,
        j.enabled 
    FROM    [msdb].dbo.sysjobs j
    JOIN    [msdb].dbo.sysjobsteps js
        ON  js.job_id = j.job_id 
    JOIN    master.dbo.sysservers s
        ON  s.srvid = j.originating_server_id
    WHERE   js.command LIKE @search
END
ELSE BEGIN 
    SELECT o.name As ''Stored Procedures''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype IN (''P'',''FN'')
    GROUP BY o.name
    ORDER BY o.name
    SELECT o.name As ''Views''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''V''
    GROUP BY o.name
    ORDER BY o.name
    SELECT t.name AS ''Tables''
    FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @search
    ORDER BY Tables
    SELECT  j.name AS ''Jobs''
    FROM    [msdb].dbo.sysjobs j
    JOIN    [msdb].dbo.sysjobsteps js
        ON  js.job_id = j.job_id 
    JOIN    master.dbo.sysservers s
        ON  s.srvid = j.originating_server_id
    WHERE   js.command LIKE @search
END
'
EXECUTE sp_executesql @TSQL

Bijwerken:
Als u een procedure hernoemt, wordt alleen sysobjects bijgewerkt, maar niet syscomments, waardoor de oude naam behouden blijft en daarom wordt die procedure niet opgenomen in het zoekresultaat, tenzij u deze verwijdert en opnieuw maakt de procedure.

Antwoord 15

Met behulp van CHARINDEX:

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 CHARINDEX('[ABD]',m.definition) >0 ;

Met behulp van PATINDEX:

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 PATINDEX('[[]ABD]',m.definition) >0 ; 

Het gebruik van deze dubbele [[]ABD] is vergelijkbaar met escapen:

WHERE m.definition LIKE '%[[]ABD]%'

Antwoord 16

probeer ook dit:

   SELECT ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION like '%\[ABD\]%'

Antwoord 17

Select distinct OBJECT_NAME(id) from syscomments where text like '%string%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 

Antwoord 18

Deze zoekopdracht is zoektekst in opgeslagen procedure uit alle databases.

DECLARE @T_Find_Text VARCHAR(1000) = 'Foo'
IF OBJECT_ID('tempdb..#T_DBNAME') IS NOT NULL DROP TABLE #T_DBNAME
IF OBJECT_ID('tempdb..#T_PROCEDURE') IS NOT NULL DROP TABLE #T_PROCEDURE
CREATE TABLE #T_DBNAME
(
    IDX int IDENTITY(1,1) PRIMARY KEY 
    , DBName VARCHAR(255)
)
CREATE TABLE #T_PROCEDURE
(
    IDX int IDENTITY(1,1) PRIMARY KEY 
    , DBName VARCHAR(255)
    , Procedure_Name VARCHAR(MAX)
    , Procedure_Description VARCHAR(MAX)
)
INSERT INTO #T_DBNAME (DBName)
SELECT name FROM master.dbo.sysdatabases
DECLARE @T_C_IDX INT = 0
DECLARE @T_C_DBName VARCHAR(255)
DECLARE @T_SQL NVARCHAR(MAX)
DECLARE @T_SQL_PARAM NVARCHAR(MAX) 
SET @T_SQL_PARAM =   
    '   @T_C_DBName VARCHAR(255)
        , @T_Find_Text VARCHAR(255)
    '  
WHILE EXISTS(SELECT TOP 1 IDX FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC)
BEGIN
    SELECT TOP 1 
    @T_C_DBName = DBName 
    FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC
    SET @T_SQL = ''
    SET @T_SQL = @T_SQL + 'INSERT INTO #T_PROCEDURE(DBName, Procedure_Name, Procedure_Description)'
    SET @T_SQL = @T_SQL + 'SELECT SPECIFIC_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION '
    SET @T_SQL = @T_SQL + 'FROM ' + @T_C_DBName +  '.INFORMATION_SCHEMA.ROUTINES  '
    SET @T_SQL = @T_SQL + 'WHERE ROUTINE_DEFINITION LIKE ''%''+ @T_Find_Text + ''%'' '
    SET @T_SQL = @T_SQL + 'AND ROUTINE_TYPE = ''PROCEDURE'' '
    BEGIN TRY
        EXEC SP_EXECUTESQL  @T_SQL, @T_SQL_PARAM, @T_C_DBName, @T_Find_Text
    END TRY
    BEGIN CATCH
        SELECT @T_C_DBName + ' ERROR'
    END CATCH
    SET @T_C_IDX = @T_C_IDX + 1
END
SELECT IDX, DBName, Procedure_Name FROM #T_PROCEDURE ORDER BY DBName ASC

Antwoord 19

/* 
    SEARCH SPROCS & VIEWS
    The following query will allow search within the definitions 
    of stored procedures and views.
    It spits out the results as XML, with the full definitions, 
    so you can browse them without having to script them individually.
*/
/*
   STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)
*/
DECLARE 
    @def_key varchar(128) = '%foo%',      /* <<< definition search key */
    @name_key varchar(128) = '%bar%',     /* <<< name search key       */
    @schema_key varchar(128) = 'dbo';     /* <<< schema search key     */
;WITH SearchResults AS (
    /* 
       STEP 2: DEFINE SEARCH QUERY AS CTE (Common Table Expression)
    */
    SELECT 
        [Object].object_id                       AS [object_id],    
        [Schema].name                            AS [schema_name], 
        [Object].name                            AS [object_name],
        [Object].type                            AS [object_type],
        [Object].type_desc                       AS [object_type_desc],
        [Details].definition                     AS [module_definition]
    FROM  
        /* sys.sql_modules = where the body of sprocs and views live */
        sys.sql_modules AS [Details] WITH (NOLOCK)
    JOIN
        /* sys.objects = where the metadata for every object in the database lives */
        sys.objects AS [Object] WITH (NOLOCK) ON [Details].object_id = [Object].object_id
    JOIN 
        /* sys.schemas = where the schemas in the datatabase live */
        sys.schemas AS [Schema] WITH (NOLOCK) ON [Object].schema_id = [Schema].schema_id
    WHERE 
        (@def_key IS NULL OR [Details].definition LIKE @def_key)      /* <<< searches definition */
        AND (@name_key IS NULL OR [Object].name LIKE @name_key)       /* <<< searches name       */
        AND (@schema_key IS NULL OR [Schema].name LIKE @schema_key)   /* <<< searches schema     */
)
/* 
   STEP 3: SELECT FROM CTE INTO XML
*/
/* 
    This outer select wraps the inner queries in to the <sql_object> root element 
*/
SELECT 
(
    /* 
        This inner query maps stored procedure rows to <procedure> elements
    */
    SELECT TOP 100 PERCENT
        [object_id]                            AS [@object_id], 
        [schema_name] + '.' + [object_name]    AS [@full_name],
        [module_definition]                    AS [module_definition]
    FROM
        SearchResults
    WHERE
        object_type = 'P'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('procedure'), TYPE
) AS [procedures],  /* <<< as part of the outer query, 
                           this alias causes the <procedure> elements
                           to be wrapped within the <procedures> element */
(
    /* 
        This inner query maps view rows to <view> elements
    */
    SELECT TOP 100 PERCENT 
        [object_id]                            AS [@object_id], 
        [schema_name] + '.' + [object_name]    AS [@full_name],
        [module_definition]                    AS [module_definition]
    FROM
        SearchResults
    WHERE
        object_type = 'V'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('view'), TYPE
) AS [views]  /* <<< as part of the outer query, 
                     this alias causes the <view> elements
                     to be wrapped within the <views> element */
FOR XML 
    PATH ('sql_objects')

Antwoord 20

Om de zoveel tijd gebruik ik dit script om erachter te komen welke procedures ik moet wijzigen, of om erachter te komen wat een kolom van een tabel gebruikt, of die tabel om wat oude rommel te verwijderen. Het controleert elke database op de instantie waarop het wordt uitgevoerd door de prachtig geleverde sp_msforeachdb.

if object_id('tempdb..##nothing') is not null
    drop table ##nothing
CREATE TABLE ##nothing
(
    DatabaseName varchar(30),
    SchemaName varchar(30),
    ObjectName varchar(100),
    ObjectType varchar(50)
)
EXEC master.sys.sp_msforeachdb 
'USE ?
insert into ##nothing
SELECT 
db_name() AS [Database],
[Scehma]=schema_name(o.schema_id), 
o.Name, 
o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o
    ON o.object_id = m.object_id
WHERE 
    m.definition like ''%SOME_TEXT%'''  
--edit this text
SELECT * FROM ##nothing n
order by OBJECTname 

Antwoord 21

-- Applicable for SQL 2005+
USE YOUR_DATABASE_NAME //;
    GO
SELECT [Scehma] = schema_name(o.schema_id)
    ,o.NAME
    ,o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o ON o.object_id = m.object_id
WHERE m.DEFINITION LIKE '%YOUR SEARCH KEYWORDS%'
GO

Antwoord 22

Een andere versie, om de zoekopdracht geschikter te maken voor verschillende coderingspraktijken.

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 UPPER(M.DEFINITION) LIKE UPPER('%Your Text%');

Antwoord 23

SELECT name , type_desc , create_date , modify_date
FROM   sys.procedures 
WHERE  Object_definition(object_id) LIKE '%High%'

Antwoord 24

U kunt ook

CREATE PROCEDURE [Search](
    @Filter nvarchar(max)
)
AS
BEGIN
SELECT name
FROM   procedures
WHERE   definition LIKE '%'[email protected]+'%'
END

en dan uitvoeren

exec [Search] 'text'