![]() SET = + 'AND ROUTINE_TYPE = ''PROCEDURE'' ' SET = + 'WHERE ROUTINE_DEFINITION LIKE ''%''+ + ''%'' ' SET = + 'FROM ' + + '.INFORMATION_SCHEMA.ROUTINES ' SET = + 'SELECT SPECIFIC_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION ' SET = + 'INSERT INTO #T_PROCEDURE(DBName, Procedure_Name, Procedure_Description)' WHILE EXISTS(SELECT TOP 1 IDX FROM #T_DBNAME WHERE IDX > ORDER BY IDX ASC)įROM #T_DBNAME WHERE IDX > ORDER BY IDX ASC ![]() IF OBJECT_ID('tempdb.#T_PROCEDURE') IS NOT NULL DROP TABLE #T_PROCEDURE IF OBJECT_ID('tempdb.#T_DBNAME') IS NOT NULL DROP TABLE #T_DBNAME This query is search text in stored procedure from all databases. WHERE js.command LIKE sp_executesql you renamed a procedure, it only updates sysobjects but not syscomments, which keeps the old name and therefore that procedure will not be included in the search result unless you drop and recreate the procedure. JOIN sys.tables t ON c.object_id = t.object_id * Table - search table name only, need to add column name */ WHERE c.text LIKE o.xtype IN (''P'',''FN'') ALTER PROCEDURE VARCHAR(255) = VARCHAR(35) = NULL is to specify the database to search, default to your current database. The first parameter is the search criterion, the search target, i.e., procedures, tables, etc. I created a procedure to search text in procedures/functions, tables, views, or jobs. You just need to write that keyword and press shortcut key.įor example: I want to search 'PaymentTable' then write 'PaymentTable' and make sure you select or highlight the written keyword in query editor and press shortcut key ctrl+4 - it will provide you full result. So next time whenever you want to find a particular text in any of the four objects like Store procedure, Views, Functions and Tables. TO FIND STRING IN ALL TABLES OF DATABASE. WHERE ROUTINE_DEFINITION LIKE ROUTINE_TYPE = 'FUNCTION' WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE FIND STRING IN ALL FUNCTION ,OBJECT_DEFINITION(OBJECT_ID) View_Definition WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE FIND STRING IN ALL VIEWS ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition Create below stored procedure and set short key, CREATE PROCEDURE AS VARCHAR(MAX))
0 Comments
Leave a Reply. |