Script to find a specific value from entire database

Some time we may need to find a specific entry (value) is present in a database or not when we do not know which table/column may have that data. 

Use below script to find a value from anywhere in a database.
Caution:

1. This script use dirty read technique to minimize the possible issues with concurrency.

2. This script is not designed for high efficiency. So make sure to estimate if it has significant impact on your environment.

set transaction isolation level read uncommitted

set nocount on

declare @rowID INT, @maxRowID INT

declare @sql NVARCHAR(4000)

declare @statements TABLE (rowID INT, SQLL NVARCHAR(MAX) COLLATE DATABASE_DEFAULT)

CREATE TABLE #results (tableName NVARCHAR(250) COLLATE DATABASE_DEFAULT,

tableSchema NVARCHAR(250) COLLATE DATABASE_DEFAULT

, columnName NVARCHAR(250) COLLATE DATABASE_DEFAULT, foundtext NVARCHAR(MAX)

COLLATE DATABASE_DEFAULT )

SET @rowID = 1

declare @searchValue NVARCHAR(100)

SET @searchValue = ‘test string’  [Replace ‘test string’  with the search criteria you need]'

DECLARE @pikkus INT

SET @pikkus=LEN(@searchValue)

;WITH MyInfo (tableName, tableSchema, columnName, XTYPE) AS (

SELECT sysobjects.name AS tableName, USER_NAME(sysobjects.uid) AS tableSchema

, syscolumns.name AS columnName, syscolumns.XTYPE

FROM sysobjects WITH(NOLOCK) INNER JOIN syscolumns WITH(NOLOCK)

ON (sysobjects.id = syscolumns.id)

WHERE sysobjects.xtype = 'U' AND sysobjects.category=0

AND sysobjects.name <> 'sysdiagrams' --MSSQL diagrams

AND syscolumns.XTYPE IN (35,99,167,175,231,239,214)

AND syscolumns.prec >= @pikkus

)

INSERT INTO @statements

SELECT row_number() over (order by tableName, columnName) AS rowID,

'INSERT INTO #results SELECT '''+tableName+''', '''+tableSchema+''',

'''+columnName+''', CAST('+columnName+' AS NVARCHAR(MAX)) FROM ['+tableSchema+'].['+tableName+'] WITH (NOLOCK) WHERE '+

CASE WHEN myInfo.XTYPE=241 --XML

THEN +'CONVERT(NVARCHAR(MAX),['+columnName+'])'

ELSE '['+columnName+']'

END+' LIKE ''%'+@searchValue+'%'''

FROM myInfo

SET @maxRowID = ( SELECT MAX(rowID) FROM @statements )

WHILE @rowID <= @maxRowID

BEGIN

SET @sql = (SELECT sqll FROM @statements WHERE rowID = @rowID )

EXEC sp_executeSQL @sql

SET @rowID = @rowID + 1

END

SELECT * FROM #results

drop table #results