Database Dictionary (3) --- Loop Databases and Servers by AI

Note: this article is published on 01/07/2025.

This series of articles will discuss Database Dictionary related issues

Database Dictionary (1) --- Schema
- Database Dictionary (1-1) --- Server, Database, Schema, Table and Column
- Database Dictionary (1-2) --- Create a System Data Dictionary Database
  - Database Dictionary (1-2-1) --- Using Dynamic SQL Script
Database Dictionary (2) --- Extended Properties
Database Dictionary (3) --- Loop Databases and Servers by AI --- this article

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
- Cross-Database Queries - SQL Server | Microsoft Learn
- Introduction to Memory-Optimized Tables - SQL Server | Microsoft Learn

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.