Differences

This shows you the differences between two versions of the page.

--- qnd:sql_server [2021/12/29 16:54] – mgupton
+++ qnd:sql_server [2021/12/30 19:25] (current) – mgupton
@@ Line 47: / Line 47: @@
 select @@SERVERNAME as [Server Name], 'Pass' as status, 'STIG V-213954' as reference
 </code>
+=== Execute Query Against Every Database ===
+  * [[https://stackoverflow.com/questions/4726899/while-loop-to-iterate-through-databases]]
+<code sql>
+use [master]
+go
+if object_id('tempdb.dbo.#database') is not null
+    drop TABLE #database
+go
+create TABLE #database(id INT identity primary key, name sysname)
+go
+set nocount on
+insert into #database(name)
+select name
+from sys.databases
+where name like '%tgsdb%' --CHANGE HERE THE FILTERING RULE FOR YOUR DATABASES!
+and source_database_id is null
+order by name
+Select *
+from #database
+declare @id INT, @cnt INT, @sql NVARCHAR(max), @currentDb sysname;
+select @id = 1, @cnt = max(id)
+from #database
+while @id <= @cnt
+BEGIN
+        select @currentDb = name
+    from #database
+    where id = @id
+    set @sql = 'select Column1, Column2 from ' + @currentDb + '.dbo.Table1'
+    print @sql
+    exec (@sql);
+    print '--------------------------------------------------------------------------'
+    set @id = @id + 1;
+END
+go
+</code>