MSSQL DB Keyword Finder

This blog covers detailed information about the solution script we can use to search for the location of specific data in multiple MSSQL databases at the table and column level.

INTRODUCTION

• This is a T-SQL script where we need to provide just the keyword we are looking for, and after executing in master DB context, it will return us a systematic result set in a table that contains the location (database, schema, table, column) where the data containing the provided keyword is present.rs.

WHY DID WE CREATE THIS SOLUTION?

Sometimes we have requests from requestors that their data related to different customers, or a specific set of key words is residing on multiple databases on the same SQL Server instance, and due to non-standard schema definitions, they are not able to find in which database, table, or column the data related to this keyword is present.

Practically, it is not easy to explore each database and table to find that information. However, this is a smart solution that will help get that information in a systematic format with minimal manual effort. This solution can be used when:

• During the organization splitting process, data must be discovered and separated.
• when a common SQL instance or database is in use by more than one customer and the new developer is not aware of which database is in use by which customer.
• Other scenarios where we would like to search for data location based on a specific keyword

WORKFLOW

IMPORTANT FILES

Please refer solution at the end of this blog

HIGH LEVEL STEPS

• Step 1: Provide a keyword to Keyword_Finder.sql and execute it on SQL Server Instance
• Step 2: Post completion of Step1, discovered data will be stored in [msdb].[dbo].[TableInfo] (i.e. discovery table)

DETAILED STEPS WITH EXAMPLE

Suppose our requirement is to search the location of all the records that contain the keyword “Rob” on instance “TESTINST.” We do not have any other information available apart from the instance name and keyword, and we have multiple databases on this instance. We can follow the following steps to discover the required data:

Steps

• Connect Target SQL Server Instance and select DB context master
• Please specify the keyword SET @TextSearch = ‘rob’ (Reference: screenshot 1)
• Execute the query
• It will store discovered data in [msdb].[dbo].[TableInfo] (Reference: screenshot 2 and 3)

Important Point:

-This query might take a good amount of time to return data based on number and size of databases on which it is running. -Following query can be used to explore data stored in msdb..TableInfo (Discovery Table)
Last column “RowSearchQuery” from the output of following query can give us query to find data at row level.

• – Query can be used to fetch data from msdb..TableInfo

SELECT [ID] , [DBName], [SchemaName], [TableName] , [ColumnName] , [SearchText] ,[ExecutionId] ,[DateInserted], 'Select * From '+ '['+ [DBName] + '].'+ '['+ [SchemaName] + '].'+'['+ [TableName] + '] where [' + [ColumnName]+ '] like ''%' +[SearchText] + '%''' RowSearchQuery FROM [msdb].[dbo].[TableInfo]

Solution

CONCLUSION

This article covers detailed information about the solution we have created to help DBA Team members with the solution script that we can use to search location of specific data in MSSQL database at table and column level.

Learn about Rackspace Managed SQL Services →