January 17, 2017 at 9:04 am
how do i get run query against all databases , I tried following but it does not seems to be working, it returns results from only one database
declare @cmd varchar(8000)='select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats'
declare @db sysname
declare @use varchar(8000)
declare c1 cursor for select name from master..sysdatabases where dbid <>2
open c1
fetch next from c1 into @db
while @@fetch_status=0
begin
set @use='use [' + cast(@db as varchar(200)) + '];'+ @cmd
print @use
exec(@cmd)
fetch next from c1 into @db
end
close c1
deallocate c1
go
exec sp_msforeachdb "select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats"
January 17, 2017 at 9:17 am
sp_msforeachdb accepts parameters as well, there's no need for your own cursor.
This would work:exec sp_msforeachdb 'USE [?];
IF DB_ID(''?'') != 2 BEGIN
SELECT ''[?]'' AS DBName, SUM (reserved_page_count) * 8 AS Estimation
FROM sys.dm_db_partition_stats;
END';
Edit: Fixed odd paste. Is it just me, or do pastes go funny on the new forum sometimes..?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 17, 2017 at 9:53 am
Thom A - Tuesday, January 17, 2017 9:17 AMsp_msforeachdb accepts parameters as well, there's no need for your own cursor.This would work:
exec sp_msforeachdb 'USE [?];IF DB_ID(''?'') != 2 BEGIN
SELECT ''[?]'' AS DBName, SUM (reserved_page_count) * 8 AS Estimation
FROM sys.dm_db_partition_stats;
END';Edit: Fixed odd paste. Is it just me, or do pastes go funny on the new forum sometimes..?
Thank you Sir
January 18, 2017 at 2:55 pm
goher2000 - Tuesday, January 17, 2017 9:04 AMhow do i get run query against all databases , I tried following but it does not seems to be working, it returns results from only one database
declare @cmd varchar(8000)='select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats'
declare @db sysname
declare @use varchar(8000)
declare c1 cursor for select name from master..sysdatabases where dbid <>2
open c1
fetch next from c1 into @db
while @@fetch_status=0
begin
set @use='use [' + cast(@db as varchar(200)) + '];'+ @cmd
print @use
exec(@cmd)
fetch next from c1 into @db
end
close c1
deallocate c1
go
exec sp_msforeachdb "select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats"
I higlighted the error in your code.
🙂
_____________
Code for TallyGenerator
January 18, 2017 at 3:53 pm
Sergiy - Wednesday, January 18, 2017 2:55 PMgoher2000 - Tuesday, January 17, 2017 9:04 AMhow do i get run query against all databases , I tried following but it does not seems to be working, it returns results from only one database
declare @cmd varchar(8000)='select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats'
declare @db sysname
declare @use varchar(8000)
declare c1 cursor for select name from master..sysdatabases where dbid <>2
open c1
fetch next from c1 into @db
while @@fetch_status=0
begin
set @use='use [' + cast(@db as varchar(200)) + '];'+ @cmd
print @use
exec(@cmd)
fetch next from c1 into @db
end
close c1
deallocate c1
go
exec sp_msforeachdb "select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats"I higlighted the error in your code.
🙂
lo, my bad.... thanks, I have fixed the code, however I am going with sp_foreachdb method.
declare @cmd varchar(8000)='select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats'
declare @db sysname
declare @use varchar(8000)
declare c1 cursor for select name from master..sysdatabases where dbid <>2
open c1
fetch next from c1 into @db
while @@fetch_status=0
begin
set @use='use [' + cast(@db as varchar(200)) + '];'+ @cmd
print @use
exec(@USE)
fetch next from c1 into @db
end
close c1
deallocate c1
January 18, 2017 at 4:13 pm
goher2000 - Wednesday, January 18, 2017 3:53 PMSergiy - Wednesday, January 18, 2017 2:55 PMgoher2000 - Tuesday, January 17, 2017 9:04 AMhow do i get run query against all databases , I tried following but it does not seems to be working, it returns results from only one database
declare @cmd varchar(8000)='select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats'
declare @db sysname
declare @use varchar(8000)
declare c1 cursor for select name from master..sysdatabases where dbid <>2
open c1
fetch next from c1 into @db
while @@fetch_status=0
begin
set @use='use [' + cast(@db as varchar(200)) + '];'+ @cmd
print @use
exec(@cmd)
fetch next from c1 into @db
end
close c1
deallocate c1
go
exec sp_msforeachdb "select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats"I higlighted the error in your code.
🙂lo, my bad.... thanks, I have fixed the code, however I am going with sp_foreachdb method.
declare @cmd varchar(8000)='select SUM (reserved_page_count) * 8 from sys.dm_db_partition_stats'
declare @db sysname
declare @use varchar(8000)
declare c1 cursor for select name from master..sysdatabases where dbid <>2
open c1
fetch next from c1 into @db
while @@fetch_status=0
begin
set @use='use [' + cast(@db as varchar(200)) + '];'+ @cmd
print @use
exec(@USE)
fetch next from c1 into @db
end
close c1
deallocate c1
here is what I am using now, for those who want to use this must run update stats so you may get better estimation
exec sp_msforeachdb "use [?];select '?' dbname, SUM (reserved_page_count) * 8 Estimated_Backup_size from sys.dm_db_partition_stats"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply