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 = '<=' --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) 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
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.