A maintenance check meant to visit every database cannot quietly skip one. The undocumented sp_MSforeachdb has a history of missed databases, especially on busy systems. A small LOCAL FAST_FORWARD cursor over sys.databases gives an explicit list, safe database names, and a result for each attempted run.
Define What Every Database Means Here
Decide whether the command belongs in user databases only, system databases too, read-only databases, or only writable availability group primaries. A script that changes schema should not run on a readable secondary. A read-only inventory can include more databases. State and access can change while the loop runs, so log skips and errors rather than claiming a perfect snapshot.
I print the selected database names before executing a change. That simple preview has caught a test database that should have been excluded and a newly restored database that the team expected to include. What would be the consequence if one database were missed or if one extra database were touched?
Filter State, Access, and AG Role
For a write operation, start with online, accessible, writable user databases. A database in an availability group should be primary on this replica. The example uses replica_id to distinguish AG databases and sys.fn_hadr_is_primary_replica for their local role. Read-only secondaries are excluded by both the write filter and role check.
SELECT d.name, d.state_desc, d.is_read_only,
d.replica_id
FROM sys.databases AS d
WHERE d.database_id > 4
AND d.state_desc = N'ONLINE'
AND d.is_read_only = 0
AND HAS_DBACCESS(d.name) = 1
AND (d.replica_id IS NULL
OR sys.fn_hadr_is_primary_replica(d.name) = 1)
ORDER BY d.name;Review the result on the actual instance. A name can be accessible at selection time and unavailable later. On a server without availability groups, verify the role expression on your supported version. For a read-only command, adjust the filter deliberately rather than reusing a write-only list without thought.
Use QUOTENAME in Dynamic SQL
Database names are identifiers, not string literals. QUOTENAME wraps each one safely in brackets and doubles a closing bracket inside a name. Build a USE statement from the quoted name, then run the command through sys.sp_executesql. Keep values as parameters inside the dynamic statement where possible; only the identifier needs concatenation.
DECLARE @database sysname = N'Sales';
DECLARE @sql nvarchar(max) =
N'USE ' + QUOTENAME(@database) + N';
SELECT DB_NAME() AS checked_database,
COUNT_BIG(*) AS table_count
FROM sys.tables;';
EXEC sys.sp_executesql @sql;The sample is read-only. Swap in a reviewed command only after previewing the target list. QUOTENAME protects identifier syntax; it does not make an unreviewed administrative statement safe. Keep the dynamic command narrow and validate it in one database first.
Log Every Database Attempt and Continue
A temp log table gives one row per database with start time, end time, status, and error message. TRY/CATCH around the execution lets the loop continue after a failure. Do not swallow the failure: return the log and alert on failed rows. The example runs the read-only table count, and its structure can carry a separately approved maintenance command.
CREATE TABLE #DatabaseRunLog
(
database_name sysname NOT NULL,
started_at datetime2(0) NOT NULL,
ended_at datetime2(0) NULL,
outcome varchar(10) NOT NULL,
error_number int NULL,
error_message nvarchar(4000) NULL
);
DECLARE @database sysname, @sql nvarchar(max);
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT d.name FROM sys.databases AS d
WHERE d.database_id > 4 AND d.state_desc = N'ONLINE'
AND d.is_read_only = 0 AND HAS_DBACCESS(d.name) = 1
AND (d.replica_id IS NULL
OR sys.fn_hadr_is_primary_replica(d.name) = 1)
ORDER BY d.name;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @database;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #DatabaseRunLog(database_name,started_at,outcome)
VALUES (@database,SYSDATETIME(),'STARTED');
BEGIN TRY
SET @sql = N'USE ' + QUOTENAME(@database) + N';
SELECT DB_NAME() AS database_name,
COUNT_BIG(*) AS table_count FROM sys.tables;';
EXEC sys.sp_executesql @sql;
UPDATE #DatabaseRunLog SET ended_at=SYSDATETIME(),
outcome='SUCCESS'
WHERE database_name=@database;
END TRY
BEGIN CATCH
UPDATE #DatabaseRunLog SET ended_at=SYSDATETIME(),
outcome='FAILED', error_number=ERROR_NUMBER(),
error_message=ERROR_MESSAGE()
WHERE database_name=@database;
END CATCH;
FETCH NEXT FROM db_cursor INTO @database;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
SELECT * FROM #DatabaseRunLog ORDER BY database_name;A command with its own transaction can leave an open transaction after an error. Design the inner command to clean up or roll back within the database, and inspect XACT_STATE() when appropriate. A TRY/CATCH loop is not a substitute for transaction handling. For durable audit records, write to a DBA utility table instead of a temporary table.
Reconcile Against the Intended List
Compare the returned log with the previewed database list and investigate missing names. A database can change state between cursor selection and execution. A FAST_FORWARD cursor does not promise a snapshot taken at open, and permissions and availability can change between rows. If a database was skipped by the initial filter, record that reason in a separate inventory. An all-green log of ten runs is not proof that the intended set had eleven databases.
I keep a count of selected, succeeded, failed, and intentionally skipped databases in the job output. For schema changes, I also verify the expected object state in each success row. A dynamic command returning without error can still do no work because a predicate matched nothing. Result verification closes that gap.
Recheck Every Database at Execution Time
The catalog filter picks each name before its command runs. Between that selection and the execution, a database can be renamed, taken offline, or moved to a secondary replica. Before a write command, validate DB_NAME() inside the dynamic batch and check the local AG role again. A failure should be logged against the original selected name. Do not hide a role change by retrying on a different node without the maintenance plan.
For a command that must cover every intended database, take a before inventory and an after inventory. Compare the names and object states. If a new database appeared mid-run, decide whether the runbook calls for a second pass. A cursor provides control and logging; it does not create a distributed transaction across all databases. When the requirement is all-or-nothing across databases, design a separate coordinated workflow rather than treating this loop as atomic. The loop deliberately commits or fails one database at a time so recovery can be targeted and visible. Save the selected list before execution and compare it with the final log, because a successful loop over an incomplete list is still an incomplete operation. Each database's work should be repeatable and independently verifiable.
Separate Reads From Changes
The sample query is deliberately read-only. A command that changes options, schemas, or data needs narrower filters and a dry run on a restored copy. Use one transaction per database where supported, and commit only after local verification. If database three fails, the log should show that databases one and two completed and database four was still attempted. That history supports a targeted retry instead of restarting the whole operation blindly.
I include a change ID in the durable log for production work. On a retry, the script checks whether that change already reached its intended state in each database. A SUCCESS message from a previous run is useful evidence, but the current schema or setting is the final authority.
Related reading on this blog: Input and Output Parameter for Dynamic SQL: Simple Example and One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?.
A database loop is not safe because it finishes, it is safe when every target and result is visible.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
