MSSQL DB Keyword Finder
by Sachin Dehran, SQL Server Database Administrator, Rackspace Technology
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.
- 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
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:
- 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)
-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]
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.