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

--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 = '<='               --Enter your search opporator here                          --'<='
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)
    (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

    --magic happens here
        '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

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


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.

  • 1
    Make sure to include the schema... – Jonathan Fite May 5 '16 at 19:27
  • I'm not a dba, could you clarify what you want the schema of? – Swazimodo May 5 '16 at 21:08
  • 1
    @Jonathan means FROM dbo.tablename instead of just FROM tablename. See this blog post and also make sure to put QUOTENAME() around direct column and table references. – Aaron Bertrand May 5 '16 at 23:22
  • hey just a quick question. Are you able to backup/restore the DB elsewhere and run your scans against the restored copy? – Ali Razeghi May 9 '16 at 15:25
  • Yeah I was not running this on PROD as I'm sure it would have a negative performance impact. – Swazimodo May 9 '16 at 18:09

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
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?

| improve this answer | |
  • 2
    You should probably mention some of the caveats in your answer about the sp_MSforeachtable if you're going to recommend it. – Erik May 5 '16 at 22:16
  • 1
    And also explain why it's different from using cursors - have you looked at the source code of sp_MSforeachtable? – Aaron Bertrand May 5 '16 at 23:22
  • 1
    I use a cursor only to go over a temp table so it wont lock any of the database that is used by other processes. If I changed to using sp_MSforeachtable what benefits could I expect to see? – Swazimodo May 6 '16 at 14:12
  • Be careful with sp_MSforeachtable, it has documented bugs and you'd be better off writing your own cursor. – Ali Razeghi May 9 '16 at 15:25

I added a few minor tweaks as suggested by the community but no one said what I was doing was wrong. I have updated the my github project and will do future enhancements there. Thanks everyone that provided some feedback!

Github project link

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.