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'
SQL Server Portal 