SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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
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.
  'IF EXISTS(SELECT 1 FROM ' + T.name + ' WHERE ' + @Criteria + ') ' +
  'SELECT ''' + T.name + ''' TableName, * FROM ' + T.name + ' WHERE ' + @Criteria 
  sys.columns C
INNER JOIN sys.tables T
  ON T.object_id = C.object_id   
  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.

Leave a Comment

Please register or log in to leave a comment.