Search entire database Recently I was working on the database of an application that had no documentation and the database did not have any foreign keys. I needed to create a script to enforce a 10 year retention policy. It would have to go through and delete data that was older than March 31, 2006.

I wanted to get an idea how much of the database was affected but that is really hard without a data diagram. I ended up writing a script that would search the entire database for dates older than March 31st, 2006.

I know this seems like brushing your teeth with a sledge hammer but is there any way to improve this query? 

--MOAQ: Mother of all Queries 
--Sam Nesbitt
--2016-03-01

--user definable
declare @colType        int
declare @searchData     varchar(10) --change this datatype as needed
declare @searchOpp      varchar(10) --enter seach opperator
declare @searchTable    varchar(100)

set @colType = 61
set @searchData = '''2006-03-31'''      --Enter search data here            
set @searchOpp = '&lt;='               --Enter your search opporator here                          --'&lt;='
set @searchTable = '%'              --Give the query a hint to the column name its looking for  --'%start_dt%','%'

IF OBJECT_ID('tempdb..##searchResults') IS NOT NULL
    DROP TABLE ##searchResults
CREATE TABLE ##searchResults(
    tableName   varchar(100)
    ,columnName varchar(100)
    ,result     datetime        --make sure this data type matches @searchData data type
)

--system use

declare @tableName  varchar(100)
declare @colName    varchar(100)

declare @searchTables table(
    tableName   varchar(100)
    ,columnName varchar(100)
)

insert into @searchTables
select QUOTENAME(d.TableName), QUOTENAME(d.ColName)
from
    (SELECT c.name AS ColName, t.name AS TableName
    FROM sys.columns c
        JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @searchTable
        and c.system_type_id = @colType --is datetime
    ) as d

--create a cursor for memory table
declare c1 cursor read_only
for select tableName, columnName
from @searchTables

print 'begin queries'

open c1
fetch next from c1
into @tableName, @colName

while @@FETCH_STATUS = 0
begin

    --magic happens here
    EXEC
    ( 
        'INSERT INTO ##searchResults select ''' + @tableName + ''', ''' + @colName + ''', d.' + @colName + ' from' +
        '(SELECT ' + @colName 
        + ' from dbo.' + @tableName
        + ' where ' + @colName + ' ' + @searchOpp + ' ' + @searchData + ' ) as d'
    )

    --dump queries to log
    print 'INSERT INTO ##searchResults select ''' + @tableName + ''', ''' + @colName + ''', d.' + @colName + ' from' +
        '(SELECT ' + @colName 
        + ' from dbo.' + @tableName
        + ' where ' + @colName + ' ' + @searchOpp + ' ' + CAST(@searchData AS varchar(256)) + ' ) as d'

    fetch next from c1
    into @tableName, @colName
end

close c1
deallocate c1

--select * from @searchTables
--select * from ##searchResults

--pull summary of results here
select tableName, columnName, count(result)
from ##searchResults
group by tableName, columnName
order by tableName, columnName

--comment this out if you want to use the results before they are dropped
IF OBJECT_ID('tempdb..##searchResults') IS NOT NULL
    DROP TABLE ##searchResults


https://github.com/Swazimodo/MOAQ 

Update
I used QUOTENAME to wrap the column and table names. I also added dbo. to the table reference. One gotcha that I found was that QUOTENAME could not be inside the EXEC function. It had to be a sting literal.
I'm wondering if you can use the undocumented stored proc sp_MSforeachtable rather than use cursors?  Here's a little bit of code that brings back the number of rows in each table. Can you modify it to do what you need to do?

use pubs2014
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
order by tablename
drop table #rowcount


And yes, I've brought both the pubs and the Northwind databases forward into SQL 2014 AND 2016...I'm nostalgic, what can I say?