Skip to content
  • There are no suggestions because the search field is empty.

T-SQL Script: Run the same query on every user database

T-SQL Script: Run the same query on every user database

DECLARE @db sysname;
DECLARE @sql nvarchar(max);

DECLARE dbs CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4      -- exclude master, model, msdb, tempdb
  AND state = 0;           -- online only

OPEN dbs;
FETCH NEXT FROM dbs INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = '
    IF EXISTS (
        SELECT 1
        FROM ' + QUOTENAME(@db) + '.sys.tables
        WHERE name = ''tblSystemParameters''
    )
    BEGIN
        SELECT
            DB_NAME() AS DatabaseName,
            BACASSecurity,
            [Site Name]
        FROM ' + QUOTENAME(@db) + '.dbo.tblSystemParameters;
    END';

    EXEC sys.sp_executesql @sql;

    FETCH NEXT FROM dbs INTO @db;
END

CLOSE dbs;
DEALLOCATE dbs;