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.
RookieDBA wrote: