Search all views’ script with a given text in a specific schema.
USE DB_TO_SEARCH SELECT SS.name AS [SCHEMA_NAME], SO.NAME AS [VIEW_NAME], SC.TEXT AS [VIEW_SCRIPT] FROM SYSCOMMENTS SC (NOLOCK) INNER JOIN SYSOBJECTS SO (NOLOCK) ON SO.ID = SC.ID INNER JOIN sys.views SV (NOLOCK) ON SO.ID = SV.object_id INNER JOIN sys.schemas SS (NOLOCK) ON SV.schema_id = SS.schema_id WHERE SC.TEXT LIKE '%TEXT_2_SEARCH%' AND SS.name='SCHEMA_2_SEARCH' --Remove this line to search all schemas
A very simple SQL Select script to search any _TEXT in stored procedures or functions which belongs to given _DB.
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION FROM _DB.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%_TEXT%' AND ROUTINE_TYPE='PROCEDURE' --OR 'FUNCTION'
Here you are another simple and very useful SQL script to search any object in all databases on the server.
Script searches all tables, views, stored procedures and functions of all databases.
SET NOCOUNT ON DECLARE @DB VARCHAR(40), @CMD VARCHAR(1000), @OBJECT_NAME VARCHAR(100) SET @OBJECT_NAME = 'MIS_MASRKOM_PARAM' DECLARE DBNAME CURSOR FOR SELECT NAME FROM SYS.DATABASES ORDER BY 1 OPEN DBNAME FETCH NEXT FROM DBNAME INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN IF @DB NOT IN ('MASTER', 'MSDB', 'MODEL', 'TEMPDB', 'DECISION_MODULE', 'PUBS', 'NORTHWIND') BEGIN SELECT @CMD = 'USE [' + @DB + '] SELECT DISTINCT ''' + RTRIM(@DB) + ''' AS ' + RTRIM(@DB) + ', SO.NAME, SO.TYPE FROM SYS.SYSOBJECTS SO (NOLOCK) JOIN SYS.SYSCOMMENTS SC (NOLOCK) ON SC.ID=SO.ID WHERE SC.TEXT LIKE ''%' + @OBJECT_NAME + '%'' ORDER BY 2, 3' PRINT RTRIM(@CMD) EXEC(@CMD) END FETCH NEXT FROM DBNAME INTO @DB END DEALLOCATE DBNAME
