Select data from multiple databases

I Have a dynamic query where i 开发者_运维技巧need to send database names as comma separated values and retreive data from different databases can u help me out..i have the following query

Decalre @DBname nvarchar(max);
Declare @Selectstring nvarchar(max);

set @Selectstring='

  select    

      Userid,
      UserName,
      CreatedOn,
      IsActive as Status,
      LastLoggedin

   from 
        '+@DBname+'.dbo.UserDetails'

    execute sp_executesql @query=@Selectstring

I am executing this using dynamic query..i want to send @Dbname value as

  set @dbname='dbname1,dbname2,dbname3'

Each DB has this table and i want to get data from all this tables from different databases..Please help me how to do this

---

Here is one way that assumes there are 3 databases on the same server (db1, db2, db3) that have a common table called: table_1. It uses sys.databases (if sql2005 or newer) to loop through the databases with a slightly difference in the way you build the @dbname string where you need to surround each db name with single quotes to correctly use it as part of an IN clause

NOTE: you can change the cursor for a while if you want.

DECLARE @dbname NVARCHAR(MAX)
SET @dbname='''db1'',''db2'',''db3'''

EXEC (
'DECLARE @db NVARCHAR(255)
DECLARE DB_CURSOR CURSOR FOR
    SELECT tbl.name FROM 
        (SELECT db.name from sys.databases db WHERE db.name IN (' + @DBname + ')) as tbl
    OPEN DB_CURSOR
    FETCH NEXT FROM DB_CURSOR
    INTO @db
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(''SELECT * FROM '' + @db + ''.dbo.table_1'')
        FETCH NEXT FROM DB_CURSOR INTO @db
    END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR'
)

---

declare @dbname varchar(max)
select @dbname = 'master, model, msdb'
declare @sql varchar(max)
select @sql = isnull(@sql,'') 
    + case when @sql is null then '' else ' union all ' end
    + 'select ''' + db.name + ''' dbname, *from ' + db.name + '.dbo.sysfiles'
from sys.databases db
where @dbname like '%' + db.name + '%'

execute (@sql)

This assumes the collation of all databases is the same and will return all results in a single result set with the source database name as a column. However, if you have databases with similar names, such as Database1 and Database10, this would return results for Database1 and Database10 if you passed in Database10 as a parameter because Database1 is a substring of Database10.

You are much better off using a table variable as input or otherwise converting the csv string into a table with a split function and then joining to the new table instead of using "where @dbname like..." syntax.