Searching a string

Searching in the haystack. Creadits Perry McKenna

I recently had to do a work which was easy but required to be done faster. I had to find all the occurrences of a particular text string which could have been stored  any column of any table in any user databases of all the servers. I already had a script which would create a stored procedure named "SearchAllTables". You can create this stored procedure in any database you want and call it from the database you want to search in that instance. Say if you have created this stored procedure in master database and want to search in database named "model" you have to execute following after creating the stored procedure:

USE model;
GO

EXEC master.dbo.SearchAllTables 'TextToSearch';
GO

But here as I said we had to search all the user databases of every SQL Server instance. So I modified the create store procedure script in such a way that you have to execute is just once on a SQL server instance OR you can execute it on a group of registered servers in your management studio if you want. Note that it would take quite long. Following is the script:

SET QUOTED_IDENTIFIER ON

USE tempdb
GO

IF OBJECT_ID('#SearchAllTables') IS NOT NULL
 DROP TABLE #SearchAllTables

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 
 'CREATE PROC dbo.SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''''
SET @SearchStr2 = QUOTENAME(''%'' + @SearchStr + ''%'','''''''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)
), ''IsMSShipped''
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
''SELECT '''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630)
FROM '' + @TableName + '' (NOLOCK) '' +
'' WHERE '' + @ColumnName + '' LIKE '' + @SearchStr2
)
END
END         
END
SELECT @@serverName AS ServerName,DB_NAME() AS DBName,ColumnName, ColumnValue FROM #Results
IF @@ROWCOUNT=0
BEGIN
select @@serverName AS ServerName,DB_NAME() AS DBName,''No columns found'' AS ColumnName, NULL as ColumnValue
END
END'

SELECT @SQL AS Stmt
INTO tempdb..#SearchAllTables;

EXEC sp_MSforeachdb 'USE [?];
DECLARE @SQLDB nvarchar(4000)
select @SQLDB=Stmt from tempdb..#SearchAllTables
IF OBJECT_ID(''dbo.SearchAllTables'') IS NOT NULL
drop proc dbo.SearchAllTables
EXEC sp_executesql @SQLDB'

EXEC sp_MSforeachdb 'USE [?];
EXEC dbo.SearchAllTables ''TextToSearch'''