Recently, the eGroup development team was tasked with a fairly complex data migration from one CMS to another due to an acquisition that occurred early in 2010. During the process it was discovered that the T-SQL that had been written was not returning the newest data that was expected from the query. The database schema was complex, abstract and because of an upgrade several years back there was data stored in different schema sets within the same database. Due to the limited time available to finish the project I took a brute-force approach and wrote a script to search the entire database for some desired values. In this case, I wanted to find any tables, with any columns, that were of a datetime column type and contained data greater than a certain date. This is not the most advanced script and is certainly not breaking any new ground but it is handy to have in the toolbox and something I will certainly use again.

<!--more-->

<em>Disclaimer: avoid running on production systems with large datasets during production hours. It queries only 1 tablecolumn at a time but could run for a lengthy period if enough columns are found that meet the desired criteria.
</em>

The script prints out a very basic resultset containing the table name, column name, the number of rows discovered meeting the search criteria, as well as (in this case) the maximum date found in that tablecolumn combination. Again, by no means a complicated script, but something that I was able to use to nail down specifically which tables and columns contained the data that I was looking for. This database had several thousand tables with 10 - 100 columns each - all very abstractly named.<em>
</em>

SET NOCOUNT ON

DECLARE @tableName varchar(100),
@tableId int,
@columnName varchar(100),
@minDate datetime,
@foundRows int,
@maxDate datetime,
@dataType int

SET @minDate = '4/1/2010'
SET @foundRows = 0
SET @dataType = 61

DROP TABLE #results
CREATE TABLE #results (numRows int, maxDate datetime)

DECLARE tableCursor CURSOR FOR

SELECT name, id
FROM sysobjects
WHERE xtype IN ('U','V')

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @tableName, @tableId

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE columnCursor CURSOR FOR
SELECT name
FROM syscolumns
WHERE id = @tableId AND xtype = dataType

OPEN columnCursor

FETCH NEXT FROM columnCursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM #results
EXEC ('INSERT #results SELECT COUNT(*), MAX(' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' &gt; ''' + @minDate + '''')

SELECT @foundRows = numRows FROM #results
SELECT @maxDate = maxDate FROM #results

IF(@foundRows &gt; 0)
BEGIN
PRINT @tableName + ' - ' + @columnName + ' - ' + CONVERT(varchar(100),@foundRows) + ' - ' + CONVERT(varchar(100),@maxDate,101)
END

FETCH NEXT FROM columnCursor INTO @columnName
END

CLOSE columnCursor
DEALLOCATE columnCursor

FETCH NEXT FROM tableCursor INTO @tableName, @tableId
END

CLOSE tableCursor
DEALLOCATE tableCursor
<div style="width: 1px;height: 1px;overflow: hidden">SET NOCOUNT ON

DECLARE @tableName varchar(100),
@tableId int,
@columnName varchar(100),
@minDate datetime,
@foundRows int,
@maxDate datetime

SET @minDate = '4/1/2010'
SET @foundRows = 0

DROP TABLE #results
CREATE TABLE #results (numRows int, maxDate datetime)

DECLARE tableCursor CURSOR FOR

SELECT name, id
FROM sysobjects
WHERE xtype IN ('U','V')

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @tableName, @tableId

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE columnCursor CURSOR FOR
SELECT name
FROM syscolumns
WHERE id = @tableId AND xtype = 61

OPEN columnCursor

FETCH NEXT FROM columnCursor INTO @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM #results
EXEC ('INSERT #results SELECT COUNT(*), MAX(' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' &gt; ''' + @minDate + '''')

SELECT @foundRows = numRows FROM #results
SELECT @maxDate = maxDate FROM #results

IF(@foundRows &gt; 0)
BEGIN
PRINT @tableName + ' - ' + @columnName + ' - ' + CONVERT(varchar(100),@foundRows) + ' - ' + CONVERT(varchar(100),@maxDate,101)
END

FETCH NEXT FROM columnCursor INTO @columnName
END

CLOSE columnCursor
DEALLOCATE columnCursor

FETCH NEXT FROM tableCursor INTO @tableName, @tableId
END

CLOSE tableCursor
DEALLOCATE tableCursor

</div>