SQL Server Portal

In my previous article, I wrote about  How to search a string value in all columns in the table and in all tables in a single database. Today we will search a string value in all columns in the table and in all tables in all databases.

Lets proceed step by step :

Step 1:
Prerequisite of Step 1 & Step 2 from previous article.

Step 2 :
Create the given below stored procedure to search the string in all columns in the table and in all tables in all databases.

CREATE PROCEDURE Find_Record_Across_Databases_Proc
@string VARCHAR(Max)
AS

DECLARE @sqlString varchar(Max)
DECLARE @Database_Name sysname

--Declare Cursor
DECLARE Database_Cursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT [name] FROM sys.databases
WHERE NAME NOT IN ('master','tempdb','model','msdb') ORDER BY NAME

OPEN Database_Cursor

FETCH NEXT FROM Database_Cursor INTO @Database_Name
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC UDP_Find_Record_Across_Tables
@Database_Name, NULL, NULL ,@string

FETCH NEXT FROM Database_Cursor INTO @Database_Name
END

CLOSE Database_Cursor
DEALLOCATE Database_Cursor

SELECT * FROM tempdb.dbo.result
GO

Example:
Once you created the above stored procedure. After that lets execute it to search a string in all columns of all tables and in all databases. Given below is a sample.

EXEC Find_Record_Across_Databases_Proc 'senior'