Look for a value within all fields on a SQL database
Now and then you need to find how to search for a value within a database and you don't know which table or field to look at.
When this happens I like to use a query to search the database and find where the example data is located.

This can be done with a simple query uses on the INFORMATION_SCHEMA.COLUMNS table of the database in question.
This table contains each field within the database, so I loop through each record and search for the content on the field within the table.
This will only look at char, ntext, nchar, text and varchar fields. If you need to alter the field types look at the initial query WHERE clause on DATA_TYPE

Query the SQL database
Set the Lookup item by replacing the '[SEARCH ITEM]' value.
Set the database you want to look in by replacing the '[DB_NAME]' value.
Once done this run the query on the server that can see the database in question


CREATE TABLE #RESULT_TABLE
(
columnName nvarchar(max)
,TableName nvarchar(max)
)

DECLARE @dynsql nvarchar(max)
DECLARE @LookUp nvarchar(max)
DECLARE @DBNAME nvarchar(max)
DECLARE @columnName nvarchar(max)
DECLARE @TableName nvarchar(max)
DECLARE cur CURSOR FOR
SELECT COLUMN_NAME, TABLE_NAME
FROM [DB_NAME].INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
ORDER BY TABLE_NAME
OPEN cur

SET @LookUp = '[SEARCH ITEM]'
SET @DBNAME = '[DB_NAME]'
FETCH NEXT FROM cur INTO @columnName, @TableName

WHILE @@FETCH_STATUS = 0 BEGIN

set @dynsql = N'INSERT INTO #RESULT_TABLE SELECT ['+@columnName+'], '''+@tableName +''' FROM '+@DBNAME+'.[dbo].[' + @tableName + '] WHERE [' + @columnName + '] LIKE ''%' + @LookUp +'%'''

EXEC sp_executesql @dynsql

FETCH NEXT FROM cur INTO @columnName, @TableName
END

CLOSE cur
DEALLOCATE cur


See results
Once the query has completed, just simply do a selected on the temp table and you will see where the result is and in what content

select * FROM #RESULT_TABLE
Created: 10/07/2017 Total Comment: 0

Comments

(Not Displayed)
Human validation
Enter 6451 number, before submitting to confirm your human
[Home] [All Blogs] [Contact] [Me]