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;
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;