Note: this article is published on 01/07/2025.
This series of articles will discuss Database Dictionary related issues
Introduction:
When we loop databases we need dynamic SQL to implement it as we discussed in Database Dictionary (1-2-1) --- Using Dynamic SQL Script.
AI approach:
This article was started writing in September, when I used dynamic SQLs to handle the loop against databases (in Section A). Later on, I used AI to do the same task, I found using AI is much easier and can get solution directly. I put the resolution in Section B and C. And I will put this article category as AI.
- A - Loop with Databases by Dynamic SQL --- my Contribution
- B - Loop with Databases by System Stored Procedure --- AI product
- C - Loop for databases in all linked servers by both SQL with Cursors and Dynamic SQL --- AI Product
A - Loop with Databases by Dynamic SQL --- my Contribution
This is what I wrote previously --- using pure dynamic SQL to loop databases:
CREATE PROCEDURE [dbo].[GetDatabases]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @sqlBody NVARCHAR(MAX) = N'';
-- Execution for one database
SELECT @sqlBody =
'
INSERT INTO SDD.dbo.[Database]
SELECT d.name, s.id
FROM
sys.databases d
INNER JOIN
SDD.dbo.[Server] s
ON
s.Id = 1
AND
d.name = DB_NAME()
AND
d.name NOT IN (SELECT i.name FROM SDD.dbo.IgnoreItem i WHERE i.[index] = 2)
'
-- Loop all databases
SELECT @sql += 'USE ' + QUOTENAME(name) + @sqlBody
FROM sys.databases
WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb');
-- Execution for the all databases
EXEC sp_executesql @sql;
END
GO
B - Loop with Databases on a Server by System Stored Procedure --- AI product
This is a AI product, what I used is Microsoft Copilot, ask for: Loop for databases in a server
To loop through databases on a SQL Server, you can use the sp_MSforeachdb
system stored procedure. This allows you to execute a command on each database without setting up a cursor. Here's a basic example:
EXEC sp_MSforeachdb 'USE ?; SELECT name FROM sys.tables;'
The result:
In this example, the ?
placeholder is replaced by each database name, and the command lists all tables in each database
If you need to perform more complex operations, you can use the WHILE
loop. Here's an example of using a WHILE
loop to iterate through databases:
DECLARE @dbName NVARCHAR(255)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('USE ' + @dbName + '; SELECT name FROM sys.tables;')
FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
C - Loop for databases in all linked servers by both SQL with Cursors and Dynamic SQL --- AI Product
This is a AI product, what I used is Microsoft Copilot, ask for: Loop for databases in all linked servers
To loop through databases in all linked servers, you can use a combination of T-SQL associated with cursors and dynamic SQL. Here's a script that demonstrates how to achieve this:
DECLARE @serverName NVARCHAR(128)
DECLARE @dbName NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)
-- Cursor to loop through all linked servers
DECLARE server_cursor CURSOR FOR
SELECT name
FROM sys.servers
WHERE is_linked = 1
OPEN server_cursor
FETCH NEXT FROM server_cursor INTO @serverName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Cursor to loop through all databases in the linked server
SET @sql = 'DECLARE db_cursor CURSOR FOR
SELECT name
FROM [' + @serverName + '].master.sys.databases'
EXEC sp_executesql @sql
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Your logic here, for example, print the database name
PRINT 'Server: ' + @serverName + ', Database: ' + @dbName
FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
FETCH NEXT FROM server_cursor INTO @serverName
END
CLOSE server_cursor
DEALLOCATE server_cursor
The result:
This script uses two cursors: one to loop through all linked servers and another to loop through all databases in each linked server. You can replace the PRINT
statement with any logic you need to apply to each database.
References:
- Transfer Data Cross Database
Note:
The references were got from online before I used AI (Copilot). The AI process is actually result oriented, instead of process oriented. If using AI, the refenreces are not necessary any more.