SQL Server – How to Pass a List of Databases to sp_MSforeachdb

dynamic-sqlsql serversql server 2014sql-server-2008-r2sql-server-2012

I have the following script that when run gives me a comma delimited list of database names.

        SET NOCOUNT ON
        declare @db_list NVARCHAR(MAX)
        DECLARE @command varchar(1000)

        SELECT @COMMAND = 'SELECT DB_NAME()'

         SELECT @db_list  = STUFF((
                                    SELECT ', ' + name FROM sys.databases
                                    WHERE name like '__Order'
                                    FOR XML PATH(''), 
                                    TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

         -- in case there are no DBs to show, then show no DB '***'
         select @db_list = COALESCE(@db_list,'***') 

         SELECT @db_list = RTRIM(LTRIM(REPLACE(@DB_LIST,',', ',''')))  


         select @db_list

this gives me the following result:

UKOrder,' ATOrder,' AUOrder

1) this is giving the wrong result set, I would like it to be

'UKOrder','ATOrder','AUOrder'

2) how can I pass @db_list to sp_MSforeachdb?

suppose I would like to run

select db_name()

inside each database in @db_list

UKOrder

ATOrder

AUOrder

Best Answer

You can't. Also you shouldn't use this undocumented, unsupported, buggy procedure in situations where it seems to work, either. I have written a better replacement here and here.

In fact, the procedure I wrote does support a parameter @database_list, in the form 'db1, db2, db3' (note no need to fuss with embedded single quotes at all), so adopting it for your needs - rather than reinventing the wheel - seems logical.

If you need help with syntax that builds a delimited string from a set for other purposes, please create a different question. If you really need to have each database name enclosed in its own quotes, the last string element in your REPLACE command should be ''',''' and you need to surround the end result with a single quote at beginning and end too.

Related Question