SQL Server Trick: Where in the World is That Data Coming From?
Do you ever have a situation where you see data from a database, but have no clue which table or field it is coming from?
Wait no longer….
Here is a script you can use that will tell you in a given database, what table and field contains that information.
For example, to find all occurances of ‘Hello’ in your data:
EXEC test.dbo.SearchAllTableForText '%Hello%'
Results:
ColumnName ColumnValue [dbo].[People].[FirstName] ABC Hello [dbo].[People].[LastName] Hello DEF
This reports that table ‘People’ and fields ‘FirstName’ and ‘LastName’ contain ‘Hello’
Here is the stored procedure to create it:
-- ============================================= -- Author: Aydin Akcasu -- Create date: 4/12/2015 -- Description: Search all tables and columns for a string. -- ============================================= CREATE PROCEDURE dbo.SearchAllTableForText @SearchStr nvarchar(300) = '%akcasu%' AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) CREATE TABLE #Results1 (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256); SET @TableName = '' DECLARE @ColumnName nvarchar(128)
WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 )
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ( 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results1 EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + ''''+ @SearchStr + '''' ) IF @@ROWCOUNT > 0 BEGIN --SELECT * FROM #Results1 INSERT INTO #Results SELECT * FROM #Results1 TRUNCATE TABLE #Results1 END END END END
SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results1 DROP TABLE #Results
END -- TEST: -- EXEC test.dbo.SearchAllTableForText '%Hello%' GO