2

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

1
  • I think that is not possible, you have to create your own cursor.
    – Krismorte
    Feb 18 '16 at 12:09
5

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.

0
1

Here a example of a cursor thats receive a list of bases

create table #tmp (base sysname)

declare @lst varchar(200)
set @lst='''master'',''msdb'''

insert into #tmp exec ('select name from sys.databases where name in ('+@lst+') ')

declare @bd sysname
declare pap_log cursor read_only forward_only for 

select * from #tmp

open pap_log
fetch next from pap_log into @bd
while @@fetch_status = 0
begin 

/*Something to do*/

fetch next from pap_log into @bd
end
close pap_log
deallocate pap_log
2

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.