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

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

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.
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!


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...