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

Comments

Popular Posts