2

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.

5

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.

|improve this answer|||||
  • Thanks, this is exactly what I was hoping for, I did a little tweaking to only include the DB I wanted and the output displayed but this is just what I needed, thank you! – Thorin May 3 '18 at 22:46
  • 1
    @Thorin and BradC: in the query to get the DB list, you should be using the 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 ](docs.microsoft.com/en-us/sql/t-sql/functions/…). – Solomon Rutzky May 4 '18 at 15:59
  • 1
    Good improvements, @SolomonRutzky. 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. – BradC May 4 '18 at 16:23
  • @Thorin and BradC: understood on both counts, and looks good. Thanks! P.S. Regarding 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 :-). – Solomon Rutzky May 4 '18 at 16:33
  • @SolomonRutzky So it looks like 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. – BradC May 8 '18 at 14:28
0

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.