Monday, 21 June 2010

SQL Server: Searching data in all tables in multiple databases

Recently, I had to deal with a weird issue which was related to possible invalid references stored in a database. I did not know much about this database so in order to remove these instances I wanted to search through all the tables in all the databases for a specific fairly long and definitely unique string value.

Then I came across this article which suggests a great solution for searching within a single database, however I had a fair amount of databases to look through so instead I wanted to see if with a little bit of tweaking I could make it work for multiple databases. It turned out I can and I thought I'd share the result.

So below is a stored procedure that you need somewhere on the server (db not important) that will search through all the tables in each of the databases you supply as a comma delimited string. You could also rework this to be a simple script rather than a stored procedure if that suits you better.

I hope it does come useful ;)

 /****** Object: StoredProcedure [dbo].[SearchAllTables]  Script Date: 06/18/2010 11:17:56 ******/  
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchAllTablesMultipleDbs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SearchAllTablesMultipleDbs]
GO
CREATE PROC SearchAllTablesMultipleDbs
(
@SearchStr nvarchar(100),
@DBListCSV nvarchar(1000)
)
AS
BEGIN
-- Copyright © 2010 Plamen Balkanski. All rights reserved.
-- Uses SearchAllTables procedure written by Narayana as a basis for searching data in multiple dbs & tables .
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- This procedure uses the modified SearchAllTables to allow searching through a list of DBs
-- Note: there is almost no validation so if you use it then ensure you pass existing DB names otherwise it will fail.
SET NOCOUNT ON
DECLARE @idx int
DECLARE @slice nvarchar(1000)
DECLARE @ResultsTable varchar(100)
DECLARE @SqlExec nvarchar(2000)
DECLARE @Delimiter char(1)
-- You can parameterise this one if you want.
SET @Delimiter = ','
SET @ResultsTable = '##MultipleDBSearchResultsTable'
-- drop/recreate the global temp table - note: must be global as otherwise it is not available in the EXECs
EXEC( 'IF (OBJECT_ID(''tempdb..' + @ResultsTable +''') IS NOT NULL) DROP TABLE ['+ @ResultsTable + ']')
EXEC ('CREATE TABLE ' + @ResultsTable + ' (DbName nvarchar(370),ColumnName nvarchar(370), ColumnValue nvarchar(3630))')
-- Split the database names and Run SearchAllTables for each
SELECT @idx = 1
IF len(@DBListCSV)>1 AND @DBListCSV is NOT null
BEGIN
WHILE @idx!= 0
BEGIN
SET @idx = charindex(@Delimiter,@DBListCSV)
IF @idx!=0
SET @slice = left(@DBListCSV,@idx - 1)
ELSE
SET @slice = @DBListCSV
IF(len(@slice)>0)
BEGIN
SELECT @SqlExec =
' USE ' + @slice +
'
DECLARE @SearchStr nvarchar(256)
SET @SearchStr = ''' + @SearchStr + '''
DECLARE @TableName nvarchar(256)
DECLARE @SearchStr2 nvarchar(110)
DECLARE
@ColumnName nvarchar(128),
@DbName nvarchar(256),
@SqlExec2 nvarchar(1000)
SET @TableName = ''''
SET @SearchStr2 =''%'' + @SearchStr + ''%''
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''''
-- In here make sure we reference each database
SELECT DISTINCT @TableName = MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)),
@DbName = MIN(QUOTENAME(TABLE_CATALOG))
FROM ' + @slice +'.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 ' + @slice +'.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
SELECT @SqlExec2 =
''INSERT INTO ' + @ResultsTable + ' SELECT '''''' + @DbName + '''''','''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630)
FROM '' + @TableName + '' (NOLOCK) ' +
' WHERE '' + @ColumnName + '' LIKE '''''' + @SearchStr2 + ''''''''
EXEC(@SqlExec2)
END
END
END'
EXEC(@SqlExec)
END
SET @DBListCSV = right(@DBListCSV,len(@DBListCSV) - @idx)
IF len(@DBListCSV) = 0 BREAK
END
END
-- show results
EXEC (' SELECT DISTINCT DbName, ColumnName, ColumnValue FROM ' + @ResultsTable)
END
/*
EXEC SearchAllTablesMultipleDbs 'SearchString', 'DbName1,DbName2'
GO
*/

No comments:

There was an error in this gadget