RSS
 

Posts Tagged ‘search stored procedures’

Text search in all stored procedures and functions

18 Jun

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'
 
No Comments

Posted in English, SQL Server

 

SQL Script to search objects in all databases

16 Jun

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
 
1 Comment

Posted in English, SQL Server