搜尋整個數據庫

最近我正在研究一個沒有文件且數據庫沒有任何外鍵的應用程序的數據庫。我需要創建一個腳本來執行 10 年保留政策。它必須檢查並刪除早於 2006 年 3 月 31 日的數據。

我想知道有多少數據庫受到了影響，但如果沒有數據圖，這真的很難。我最終編寫了一個腳本，可以在整個數據庫中搜尋早於 2006 年 3 月 31 日的日期。

我知道這看起來像是用大錘刷牙，但是有什麼辦法可以改進這個查詢嗎？

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

https://github.com/Swazimodo/MOAQ

更新 我使用 QUOTENAME 來包裝列名和表名。我還添加了*dbo。*表參考。我發現的一個問題是 QUOTENAME 不能在 EXEC 函式中。它必須是一個刺痛的文字。

我按照社區的建議添加了一些小的調整，但沒有人說我做錯了。我已經更新了我的 github 項目，並將在那裡進行未來的改進。感謝所有提供回饋的人！

Github 項目連結

我想知道您是否可以使用未記錄的儲存過程 sp_MSforeachtable 而不是使用游標？這裡有一些程式碼可以返回每個表中的行數。你能修改它來做你需要做的事情嗎？

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

是的，我已經將 pubs 和 Northwind 數據庫都帶到了 SQL 2014 和 2016 中……我很懷舊，我能說什麼？

引用自：https://dba.stackexchange.com/questions/137680