Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am trying to run a query like the one below across multiple databases
select ParamValue from Table1 where paramname = 'SYSTEM_LICENSE'
Each DB is named something like the one below and some DB should be excluded.
"Customer_123"
I looked at using sp_MSforeachdb but it isn't supported and it wasn't working well. I tried figuring out how to write a cursor but was unsuccessful. At this point just looking for some help from someone who can actually write what I am looking to do.
Here's the "do something on each db" template I use, slightly adapted to your example:
--Create a table to hold the query results
CREATE TABLE #licenselist
(dbid smallint, dbname sysname, system_license varchar(99));
DECLARE @fixedname nvarchar(150);
--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
SELECT REPLACE(QUOTENAME(name),'''','''''') --For db names with annoying characters
FROM master.sys.databases
WHERE state_desc = 'ONLINE'
AND HAS_DBACCESS(name) = 1 --Added at @SolomonRutzky's suggestion
AND name NOT IN ('master','msdb','model','tempdb');
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @fixedname;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('USE ' + @fixedname + ';' +
'INSERT INTO #licenselist
SELECT DB_ID() as dbid, DB_NAME() as dbname, ParamValue as system_license
FROM Table1
WHERE paramname = ''SYSTEM_LICENSE'';');
FETCH NEXT FROM dbCursor INTO @fixedname;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;
SELECT * FROM #licenselist
DROP TABLE #licenselist
EDIT: streamlined a bit, removed the raw @dbname variable and instead fixed it in the original query.
state_desc column of sys.databases since you are already selecting from that system catalog view, rather than invoking a function to get that same value in a round-about way. Also, you should ideally also check permissions via another WHERE condition checking [HAS_DBACCESS ](learn.microsoft.com/en-us/sql/t-sql/functions/…).
May 4, 2018 at 15:59
DATABASEPROPERTYEX was probably left over from older versions of my script that used the (now deprecated) sysdatabases, which doesn't have that column. Since I no longer have any SQL 2000 servers (yeah!) I can remove it. As a DBA, HAS_DBACCESS will always be 1 for me, but I can see the utility in a general use script.
HAS_DBACCESS and being a DBA, yeah, the idea is that queries like this become templates pasted into all scripts, prior to dealing with who will execute it, so I like having it there as it doesn't hurt and helps prevent errors that break the process when pasted into a general-use script :-).
May 4, 2018 at 16:33
HAS_DBACCESS isn't just a permission check, and could even replace our state_desc = 'ONLINE' filter entirely, since it returns 0 if the database is offline or suspect.
Thanks again, slightly modified to only query the DB I wanted and output only results with YES.
--Create a table to hold the query results
CREATE TABLE #licenselist
(dbname sysname, system_license varchar(99));
DECLARE @dbname SYSNAME;
DECLARE @fixedname nvarchar(150);
--Include or exclude whatever databases you want
DECLARE dbCursor CURSOR FOR
SELECT name FROM master.sys.databases
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
AND name like 'SOMETHING_%'
AND name not like '%_SOMETHINGELSE'
--AND name NOT IN ('master','msdb','model','tempdb');
OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
--Fix for invalid characters in DB names
SELECT @fixedname = REPLACE(QUOTENAME(@dbname),'''','''''');
EXEC ('USE ' + @fixedname + ';' +
'INSERT INTO #licenselist
SELECT DB_NAME() as dbname, ParamValue as system_license
FROM TABLE1
WHERE paramname = ''STRING_VALUE'';');
FETCH NEXT FROM dbCursor INTO @dbname;
END;
CLOSE dbCursor;
DEALLOCATE dbCursor;
SELECT *
FROM #licenselist
where system_license='YES'
ORDER by dbname desc
DROP TABLE #licenselist