October 4, 2017 at 5:18 pm
Hi,
Can you suggest some T-SQL code to automate so that a script say select * from employee can be run on about 200 databases in a sql server instance and output mailed via a csv file?
I would like to use a sproc and then use sql agent to exec that sproc every AM at say 8.
Thanks
October 4, 2017 at 6:11 pm
sqlguy80 - Wednesday, October 4, 2017 5:18 PMHi,Can you suggest some T-SQL code to automate so that a script say select * from employee can be run on about 200 databases in a sql server instance and output mailed via a csv file?
I would like to use a sproc and then use sql agent to exec that sproc every AM at say 8.
Thanks
You post is titled, "sp_msforeachdb". That is certainly an one option. See this link: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/
sp_msforeachdb is undocumented and unsupported. If that's a proble you can use this:
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
-- Itzik Ben-Gan 2001
October 5, 2017 at 3:19 pm
Sticking just with TSQL, here are a couple of other options...
Assuming your target tables have the same schema you could fudge together other alternatives like creating a holding table and running something like this...DECLARE @Query NVARCHAR(MAX)
SELECT @query = STUFF((SELECT ' INSERT INTO yourTable SELECT * FROM [' + name + '].[dbo].[employees];' FROM [sys].[databases] where database_id > 4
FOR XML PATH(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
EXEC (@query)
If your database list is fairly static you could write it all out...SELECT * FROM [PirateDatabase1].[dbo].[employees]
UNION ALL
SELECT * FROM [PirateDatabase2].[dbo].[employees]
UNION ALL
SELECT * FROM [PirateDatabase3].[dbo].[employees]
UNION ALL
SELECT * FROM [PirateDatabase4].[dbo].[employees]
You could stuff that in a view if you like...
I never said it was pretty.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply