sql-server information_schema routines - SQL Serverのストアドプロシージャ内のテキストを検索する





11 Answers

このリクエストを試してください:

クエリ

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

私はすべてのデータベースストアドプロシージャからテキストを検索したい。 私は以下の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]%';

大括弧を含むすべてのストアドプロシージャで[ABD]を検索したいが、適切な結果が得られていない。 これを達成するためにクエリを変更するにはどうすればよいですか?




私は通常、それを達成するために以下を実行します:

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



SQL Serverを使用するための良い習慣。

保存手順の下にして、そのようなボトムイメージのショートキーを設定し、

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 '%'+@strFind+'%'
    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 '%'+@strFind+'%'
    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 '%'+@strFind+'%'
               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 '%'+@strFind+'%'
        ORDER BY
               Table_Name
    END
END

Now - ショートキーを以下のように設定し、

次回は、 Store procedureViewsFunctionsTablesような4つのオブジェクトのいずれかに特定のテキストを見つけたいときはいつでもそうです。 そのキーワードを書いてショートキーを押すだけです。

たとえば、 「PaymentTable」を検索し、クエリエディタで「PaymentTable」と入力し、ショートカットctrl+4を押すと、完全な結果が得られます。




これを「汚れた」代替案と見なしてください。しかし、これは私がDBプロジェクトに精通していなかったときに何度も後悔していました。 時には、すべてのSPの中で文字列を検索しようとしてい 、関連するロジックのいくつかが関数とトリガの間に隠れている可能性があることを忘れてしまったのです。

あなたのMSSMSからあなたのDBを右クリックし、 Tasks -> Generate Scriptsウィザードを選択して、すべてのSP、Fn、トリガを1つの.sqlファイルに出力することができます。

トリガーも必ず選択してください!

次に、SublimeまたはNotepad使用して、検索する文字列を検索します。 私はこれが非常に非効率的かつ編集的アプローチかもしれないが、それが動作することを知っている:)




それはあなたを助けるかもしれない!

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 '%['+@SEARCH_TEXT+']%'
      ORDER BY TYPE_DESC



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



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



プロシージャ、テーブル、ビュー、またはジョブを検索するプロシージャを作成しました。 最初のパラメータ@searchは検索基準です。@targetは検索対象です。つまり、プロシージャ、テーブルなどです。指定されていない場合は、allを検索します。 @dbは、指定されていなければ、現在のdbを検索するデータベースを指定します。 ここでは、動的SQLのクエリです。

ALTER PROCEDURE [dbo].[usp_find_objects]
(
    @search VARCHAR(128),
    @target VARCHAR(128) = NULL,
    @db VARCHAR(20) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(30)

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 = ''P''
    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 = ''P''
    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



たびにこのスクリプトを使用して、どのprocsを変更するか、またはテーブルの列を使用するものやそのテーブルを使用して古いジャンクを削除する方法を特定します。 このスクリプトは、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 



このクエリは、すべてのデータベースからのストアドプロシージャの検索テキストです。

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



あなたも使用することができます

CREATE PROCEDURE [Search](
    @Filter nvarchar(max)
)
AS
BEGIN

SELECT name
FROM   procedures
WHERE   definition LIKE '%'+@Filter+'%'

END

実行してください

exec [Search] 'text'



Related