Search Full DataBase

The searches in the database are very common, but sometimes it doesn’t know the table’s name or column’s name, but only the value to search.

I wrote a SQL Script that executes the search in the database, then in all columns.

The SQL Script can work on tables of sys namespace or on INFORMATION_SCHEMA namespace, in this situation they’re equivalent.

The first step is the search of the tables, columns and type’s columns; in this example I’m searching for a string, then I apply a first filter to search.

The query is:

  • SELECT name, system_type_id FROM sys.types WHERE NAME LIKE ‘%char%’

With this script I obtain the system types of SQL Server that represents the String. The result is shown in the Table:

name system_type_id
varchar 167
char 175
nvarchar 231
nchar 239

The query that searcheas the value specifiied in the all database is:

'''SELECT count(*) FROM ' + + ' WHERE ' + + ' Like ''%Ma%''' As Query
FROM sys.tables sys_tables INNER JOIN
sys.objects sys_objects ON sys_tables.object_id = sys_objects.object_id INNER JOIN
sys.columns sys_columns ON sys_objects.object_id = sys_columns.object_id and sys_tables.object_id = sys_objects.object_id
WHERE sys_tables.type = 'u' AND
sys_columns.system_type_id IN (167, 175, 231, 239)

In the third column you have the query. You can copy all results of third column and execute the SQL Script that verifies the effective result.

For this example, I’ve used Northwind and the result set has 52 rows, but in the real scenario there are hundreds of tables then you’ve a result set with thousands of rows.

It’s very difficult to find the result that you want, you can filter the final result with a SQL Script.

It’s possible filter the result set by SQL Server, I describe the process step by step:

  • Table Variable: insert the result of query in the temporary table.
  • Call Stored Procedure “sp_executesql” for every row in the table with the query that it is saved in the temporary table, then it update the table with the result.
  • It filter the updated result of temporary table

You can downloaded the code from click here.

Leave a Reply

Your email address will not be published. Required fields are marked *