In some of my previous project, I had to find out whether a particular data existed on anywhere in any database. By that I mean, if I wanted to find out whether a particular data, for example, "manager" made in to any table. It's a trivial issue if you know the database table structure and column names. What if you did not have that benefit and wanted to do a massive manhunt for the data? I have written similar script such as the one below, and forgot to keep track of it in my library. Finally, my slowly deteriorating and dull memory served as a motivation for this post. The way it works is a) by getting list of all available databases, b) using dynamic sql to capture tables and column information, c) comparing sought after data against any column that can be converted to varchar type. If you need more explanation then comment please.
Here is the snippet:
SET NOCOUNT ON
DECLARE @SoughtAfterValue VARCHAR(8000)
SET @SoughtAfterValue ='Manager'
DECLARE @Tmp TABLE
(
ID INT IDENTITY(1,1)
, DBName VARCHAR(255)
)
INSERT INTO @Tmp(DBName)
SELECT name
FROM sys.sysdatabases
DECLARE @i INT
DECLARE @total INT
SET @i = 1
SELECT @total = COUNT(*)
FROM @Tmp
DECLARE @sql Nvarchar(max)
WHILE @i <= @total
BEGIN
set @sql= N'
use ' +(
SELECTDBName FROM @Tmp WHEREID = @i
) + '
DECLARE @TmpQry TABLE
(
ID INT IDENTITY(1, 1)
, Qry VARCHAR(8000)
)
if exists
( SELECT
*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = '''
+
(
SELECTDBName FROM @Tmp WHEREID = @i
)
+ '''
)
BEGIN
INSERT INTO @TmpQry (Qry)
SELECT
''SELECT CAST('' +COLUMN_NAME + '' AS VARCHAR(8000))[Result] '' +
''FROM '
+
(
SELECTDBName FROM @Tmp WHEREID = @i
)
+ '..' + ''' + TABLE_NAME
+ '' WHERE CAST('' +COLUMN_NAME + '' AS VARCHAR(8000)) like ''''%' +@SoughtAfterValue + '%''''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE NOT IN
(
''binary'',''varbinary'', ''image'', ''geography'', ''geometry'', ''timestamp'',
''xml'',''hierarchyid'', ''sql_variant''
)
END
DECLARE @i INT
DECLARE @Total INT
DECLARE @CurrentQryVARCHAR(8000)
DECLARE @CurrentQry2VARCHAR(8000)
SET @i = 1
SELECT @Total = COUNT(*) FROM@TmpQry
WHILE @i <= @Total
BEGIN
SELECT @CurrentQry = Qry
FROM @TmpQry WHERE ID =@i
SET @CurrentQry2 = ''ifexists('' + @CurrentQry + '') begin select ''''''+ REPLACE(@CurrentQry,'''''''', '''''''''''') + '''''' [Query Ran] '' + @CurrentQry + '' end''
exec(@CurrentQry2)
SET @i = @i + 1
CONTINUE
END
'
exec(@sql)
SET @i = @i + 1
CONTINUE
END