A few years back I needed to search for data in some DBs I wasn't familiar with. So I created a quick and dirty script to do this. I put together a few versions of it, one for string/character based stuff, another for numerics and so on as needed. Here is what I did for searching for strings. I used dynamic sql to put together the queries and opted to PRINT them out rather than select them to rows so there are a few tricks I needed to use to implement this. It does still have some bugs (like field type of text isn't implemented) I haven't worked out but thought I would present it as one way of doing this sort of thing.
--Script to look for varchar data in tables
--Run this replacing the value in @dataLookingFor with the exact string you are looking for
--check the Messages tab for info on what tables have the string
DECLARE
@staticSql nvarchar(max)
,@dynamicSql nvarchar(max)
,@combinedSql nvarchar(max)
,@dataLookingFor varchar(100)
,@parmDefinition nvarchar(100)
,@dateStart datetime
,@dateEnd datetime
,@dateTot datetime
,@print varchar(100)
;
SELECT @dateStart = GETDATE()
SET @dataLookingFor = 'SomeString';
SET @parmDefinition = N'@data varchar(100)'; --change to size/type needed
SELECT
@dynamicSql = theSql.value('/row[1]/column_alias[1]', 'nvarchar(max)')
FROM
(
SELECT column_alias
FROM
(
SELECT N'IF EXISTS(SELECT 1 FROM '
+ N'dbo.[' + C.TABLE_NAME + N']'
+ N' WHERE [' + C.COLUMN_NAME + N'] = @data) BEGIN '
+ N' SELECT @cnt = CAST(COUNT(*) AS varchar(20)) FROM ' + N'dbo.[' + C.TABLE_NAME + N']'+ N' WHERE [' + C.COLUMN_NAME + N'] = @data;'
+ N' RAISERROR (''' + C.TABLE_NAME + '.' + C.COLUMN_NAME + N' has data - %d times'', 0, 1, @cnt) WITH NOWAIT;'
+ N' END'
+ N';'
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN
INFORMATION_SCHEMA.TABLES T
ON C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
--add tables and column types that you don't want to check (columns should remain as is since they are not string data)
-- AND T.TABLE_NAME NOT IN ('???')
AND C.DATA_TYPE NOT IN (
'bigint'
,'decimal'
,'int'
,'numeric'
,'smallint'
,'money'
,'tinyint'
,'smallmoney'
,'bit'
,'float'
,'real'
,'datetime'
,'smalldatetime'
,'binary'
,'image'
,'varbinary'
,'cursor'
,'timestamp'
,'sql_variant'
,'uniqueidentifier'
,'table'
,'xml' --got this type below
,'text' --need to implement this?
)
FOR XML PATH(''), TYPE
) tab_alias (column_alias)
FOR XML RAW, TYPE
) tab_alias2 (theSql)
SELECT @dateEnd = GETDATE()
SELECT @dateTot = @dateEnd - @dateStart
SELECT @print = 'Time elapsed for running create query: ' + CONVERT(VARCHAR(20),@dateTot,114)
--note that @cnt has to be create before the above query can use it in the query
--so we concatenate @staticSql with @dynamicSql
SET @staticSql = N' DECLARE @cnt int;'
+ N' RAISERROR (''Data being checked for - %s'', 0, 1, @data) WITH NOWAIT;'
+ N' RAISERROR ('''', 0, 1) WITH NOWAIT;'
SET @combinedSql = @staticSql + N' ' + @dynamicSql
-- PRINT @combinedSql
SELECT @dateStart = GETDATE()
EXEC sp_executesql
@combinedSql
,@parmDefinition
,@data=@dataLookingFor
;
SELECT @dateEnd = GETDATE()
PRINT ''
PRINT @print
SELECT @dateTot = @dateEnd - @dateStart
SELECT @print = 'Time elapsed for running query: ' + CONVERT(VARCHAR(20),@dateTot,114)
PRINT @print
--look in the XML now
SELECT @dateStart = GETDATE()
SELECT
@dynamicSql = theSql.value('/row[1]/column_alias[1]', 'nvarchar(max)')
FROM
(
SELECT column_alias
FROM
(
SELECT N'IF EXISTS(SELECT 1 FROM '
+ N'dbo.[' + C.TABLE_NAME + N']'
+ N' WHERE CAST([' + C.COLUMN_NAME + N'] AS varchar(max)) LIKE ''%' + @dataLookingFor + '%'') BEGIN '
+ N' SELECT @cnt = CAST(COUNT(*) AS varchar(20)) FROM ' + N'dbo.[' + C.TABLE_NAME + N']'+ N' WHERE CAST([' + C.COLUMN_NAME + N'] AS varchar(max)) LIKE ''%' + @dataLookingFor + '%'';'
+ N' RAISERROR (''' + C.TABLE_NAME + '.' + C.COLUMN_NAME + N' has data - %d times'', 0, 1, @cnt) WITH NOWAIT;'
+ N' END'
+ N';'
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN
INFORMATION_SCHEMA.TABLES T
ON C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
--add tables and column types that you don't want to check (columns should remain as is since they are not string data)
-- AND T.TABLE_NAME NOT IN ('???')
AND C.DATA_TYPE ='xml'
FOR XML PATH(''), TYPE
) tab_alias (column_alias)
FOR XML RAW, TYPE
) tab_alias2 (theSql)
SELECT @dateEnd = GETDATE()
SELECT @dateTot = @dateEnd - @dateStart
SELECT @print = 'Time elapsed for running create query: ' + CONVERT(VARCHAR(20),@dateTot,114)
--note that @cnt has to be create before the above query can use it in the query
--so we concatenate @staticSql with @dynamicSql
SET @staticSql = N' DECLARE @cnt int;'
+ N' RAISERROR (''Data being checked for - %s'', 0, 1, @data) WITH NOWAIT;'
+ N' RAISERROR ('''', 0, 1) WITH NOWAIT;'
SET @combinedSql = @staticSql + N' ' + @dynamicSql
-- PRINT @combinedSql
SELECT @dateStart = GETDATE()
EXEC sp_executesql
@combinedSql
,@parmDefinition
,@data=@dataLookingFor
;
SELECT @dateEnd = GETDATE()
PRINT ''
PRINT @print
SELECT @dateTot = @dateEnd - @dateStart
SELECT @print = 'Time elapsed for running query: ' + CONVERT(VARCHAR(20),@dateTot,114)
PRINT @print