About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Wednesday 6 March 2013

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. However, there are times when we need to find out records matching a criteria from all the tables of a SQL Database and today I will explain you a simple way to retrieve those records.

Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process.

I quickly opened up the SSMS and came up with the below script -

USE <DBName> --Replace this with the actual DBName
Go
 
DECLARE @ColumnName AS VARCHAR(50) = 'CreatedOn' --The name of the column on which you need to put the criteria
DECLARE @Criteria AS VARCHAR(50) = 'CONVERT(DATE,' + @ColumnName + ') >= ''20130225''' -- The Actual criteria/WHERE Clause of the query
 
--The below will list the TSQL Statements which could be copied & executed in a separate query window.
SELECT 
  'IF EXISTS(SELECT 1 FROM ' + T.name + ' WHERE ' + @Criteria + ') ' +
  'SELECT ''' + T.name + ''' TableName, * FROM ' + T.name + ' WHERE ' + @Criteria 
FROM 
  sys.columns C
INNER JOIN sys.tables T
  ON T.object_id = C.object_id   
WHERE 
  C.name = @ColumnName


The above script will list down the SELECT statements which could be copied and executed in a separate query window connecting to the same Database. On execution, you will get the list of records from each table base on the specified criteria.


Hope, this helps!

No comments:

Post a Comment