Run query against multilpe database

I have to run a script I have written against multiple databases (about 40) - how would I do this? Once I have the results I need them all to be unioned into one table.

Part of the script is below...

Thanks for any help.

Code Snippet

USE db1

SELECT * FROM Users
WHERE (Users.user_type = '1.0' AND Users.user_id <> 'itadmin')
OR (Users.user_id LIKE '%test%' OR Users.user_forename LIKE '%test%' OR Users.user_surname LIKE '%test%')

If the databases are on the same server, fully qualify the field: i.e. databasename.tablename.fieldname

If they are on different servers, you'll need to use sp_addlinkedserver which, if you're not the DBA, you might have access to.

In this case, I normally just port the tables on to a development database usign the Import/Export wizard and discard them when I'm done.

Adamus

|||The problem with this is that the script is really long, and with so many databases, adding the full link to the db is not practical. I am currently trying to use the sp_msforeachdb proc, which seems a bit better, but still not really there yet!
Thanks anyway.
|||Ok, so I've got a bit further... Have managed to get all of the info from all of the databases - but does anyone know how I could union all of the results gained from this piece of code.
Thanks

declare @.cmd1 varchar(500)
set @.cmd1 =
' if ''?'' NOT IN (''tempdb'')
USE ?
SELECT ''?'',Users.* FROM Users
WHERE (Users.user_type = ''1.0'' AND Users.user_id <> ''itadmin'')
OR (Users.user_id LIKE ''%test%'' OR Users.user_forename LIKE ''%test%'' OR Users.user_surname LIKE
''%test%'')'
exec sp_MSforeachdb @.command1=@.cmd1

|||

Best of luck

Adamus

|||

This seemed like a great thing to do with CROSS APPLY

But, alas, I couldn't find a way to initiate dynamic SQL from within a function

I did come up with this....it's not elegant, but it's functional

Code Snippet

create table #dblist ( dbname varchar(100) )

insert into #dblist

select '[db1]' union all

select '[db2]' union all

select '[db3]' union all

.

.

.

select '[db40]'

DECLARE @.dbname varchar(100),

@.selectfrom nvarchar(20),

@.scriptbody nvarchar(4000),

@.sql nvarchar(200)

--@.selectfrom is the part of the script before the dbname qualifier

SET @.selectfrom = N'select * from '

--@.scriptbody is the .dbo.tablename {rest of script}

SET @.scriptbody = N'.dbo.mytable'

--This table variable holds the results of all the query executions

-- make it a perm table or a temp table or whatever.

DECLARE @.results table ( id int, [name] varchar(100) )

DECLARE db_csr CURSOR FOR

SELECT dbname FROM #dblist --probably make #dblist a permanent table

OPEN db_csr

FETCH NEXT FROM db_csr

INTO @.dbname

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT ' Start: ' + @.dbname

PRINT getdate()

--this is the guts

SET @.sql = @.selectfrom + @.dbname + @.scriptbody

INSERT INTO @.results

EXEC (@.sql)

PRINT ' End: ' + @.dbname

PRINT getdate()

FETCH NEXT FROM db_csr

INTO @.dbname

END

CLOSE db_csr

DEALLOCATE db_csr

--============

--review final product

SELECT *

FROM @.results

Let me know if you have any questions.

|||

Here's a version that uses USE DB

plus a couple of variable tweaks

Code Snippet

create table #dblist ( dbname varchar(100) )

insert into #dblist

select '[db1]' union all

select '[db2]' union all

select '[db3]' union all

.

.

.

select '[db40]'

DECLARE @.dbname varchar(100),

@.use nvarchar(20),

@.scriptbody nvarchar(max),

@.sql nvarchar(max)

--@.use is the part of the script before the dbname qualifier

SET @.use = N'use '

--@.scriptbody is the rest of script

SET @.scriptbody = N'; select * from mytable;'

--This table variable holds the results of all the query executions

-- define the columns as you need it

-- make it a perm table or a temp table or whatever.

DECLARE @.results table ( id int, [name] varchar(100) )

DECLARE db_csr CURSOR FOR

SELECT dbname FROM #dblist --probably make #dblist a permanent table

OPEN db_csr

FETCH NEXT FROM db_csr

INTO @.dbname

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT ' Start: ' + @.dbname

PRINT getdate()

--this is the guts

SET @.sql = @.use + @.dbname + @.scriptbody

INSERT INTO @.results

EXEC (@.sql)

PRINT ' End: ' + @.dbname

PRINT getdate()

FETCH NEXT FROM db_csr

INTO @.dbname

END

CLOSE db_csr

DEALLOCATE db_csr

--============

--review final product

SELECT *

FROM @.results

|||

Kate

SSIS is designed to handle these types of tasks as well.

There is a FOREACH process you can use as well as accessing various data sources.

|||As Dale indicates, SSIS is most likely the best solution for your problem.|||Can anyone point me in the direction of a good tutorial/explanation of SSIS? I've not used it before with SQL Server.
Cheers
K
|||

You might find that a visit to http://www.sqlis.com/ is worth your time and effort.

Also:

http://msdn2.microsoft.com/en-us/library/ms167031.aspx

http://digg.com/programming/Tutorial_1_Creating_Your_First_SSIS_Package_Using_SQL_Server_2005_Beta_2

|||Thanks for that Arnie - I will look at them now.
K
|||Thanks for this Dale - I don't have SSIS on my PC, so this has been used to create my answer.