July 18, 2013 at 10:17 am
I need to run this query in 100 databases. How to make this as dynamic script and run in all databases at once and get results?
SELECT * FROM Docs WHERE (VirusStatus > 0) AND (VirusStatus IS NOT NULL)
July 18, 2013 at 10:31 am
You could use something like this.
DECLARE @SQL varchar(8000) = ''
SELECT @SQL = @SQL + 'SELECT * FROM ' + QUOTENAME(name) + '..Docs WHERE (VirusStatus > 0) AND (VirusStatus IS NOT NULL); ' + CHAR(10) + 'GO' + CHAR(10)
FROM sys.databases
PRINT @SQL
EXEC( @SQL)
July 18, 2013 at 10:39 am
If you want to run some statement in mulitple databases simultaneously then you will need to write some sort of application which can open connections to your multiple databases at the same time and execute this query.
From within SQLServer (eg. SSMS) you can run the same query for each of wanted databases but it will happen one by one.
Are all your databases on the same server?
You can use undocumented sp_MSforeachdb stored proc to run statement for each DB on the server (name filter can be provided), however it's not recommended for use...
Other, better way, to achieve the same whould be based on cursor over of sys.databases table:
declare @dbName varchar(255)
declare @sql varchar(max)
declare dbs cursor local fast_forward
for select name from sys.databases where name like 'My%' -- put your required condition for db selection
open dbs
fetch next from dbs into @dbName
while @@FETCH_STATUS = 0
begin
set @sql = 'SELECT * FROM ' + @dbName + '..Docs WHERE (VirusStatus > 0) AND (VirusStatus IS NOT NULL)'
exec (@sql)
fetch next from dbs into @dbName
end
close dbs
deallocate dbs
please note: use of cursor here is totally justified!
July 18, 2013 at 10:44 am
Eugene Elutin (7/18/2013)
please note: use of cursor here is totally justified!
It's justified, but my approach is shorter. Maybe I'm just lazy. 😀
July 18, 2013 at 10:52 am
Luis Cazares (7/18/2013)
Eugene Elutin (7/18/2013)
please note: use of cursor here is totally justified!It's justified, but my approach is shorter. Maybe I'm just lazy. 😀
Yeah, for just executing one line of SQL I wouldn't use cursor too. However, something tells me that a bit more should happen there in between of executing required line of SQL for each database, therefore I went for a cursor aproach.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply