table sizes for all d/b on the server
-
Hi all,
I am trying to retrieve table information in every d/b on the server, however in my code below, for some reason d/b name is not switched, so sp_MSForEachTable runs for the same d/b multiple times. What am I doing wrong?
(I modified sp_spacedused to return extra values: db_name, object_id, schema)
declare @set nvarchar(100)declare @db_name varchar(128)
declare db_cursor cursor for
select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')
IF object_id('tempdb..#TableSize') IS NOT NULL
begin
DROP TABLE #TableSize
end
create table #TableSize (database_name varchar(150),
id int,
table_schema varchar(150),
table_name varchar(150),
num_rows int,
reserved varchar(150),
data varchar(150),
index_size varchar(150),
unused varchar(150)
)
open db_cursor
fetch next from db_cursor into @db_name
while @@FETCH_STATUS = 0
begin
select @set = 'USE ['+@db_name+']'
exec sp_executesql @set
insert into #TableSize
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused2 ''?'''
fetch next from db_cursor into @db_name
end
close db_cursor
deallocate db_cursor
select * from #TableSize
drop table #TableSize
Thanks,
Eugene
-
the USE <dbname> and the EXEC sp_....have to be in the same EXEC statment....otherwise the fiirst one, just use <dbname> goes out of scope when the the eXEC finishes (instantly)
declare @cmd varchar(500);
@cmd=''USE ['+@db_name+']; EXEC sp_MSforeachtable @command1='EXEC sp_spaceused2 ''?'' ; '
;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for reply, but I am getting the following error:
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_spaceused2'.
declare @cmd nvarchar(100)declare @db_name varchar(128)
declare db_cursor cursor for
select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')
IF object_id('tempdb..#TableSize') IS NOT NULL
begin
DROP TABLE #TableSize
end
create table #TableSize (database_name varchar(150),
id int,
table_schema varchar(150),
table_name varchar(150),
num_rows int,
reserved varchar(150),
data varchar(150),
index_size varchar(150),
unused varchar(150)
)
open db_cursor
fetch next from db_cursor into @db_name
while @@FETCH_STATUS = 0
begin
set @cmd='USE ['+@db_name+']; EXEC sp_spaceused2 ''?'' ; '
insert into #TableSize
EXEC sp_MSforeachtable @command1=@cmd
fetch next from db_cursor into @db_name
end
close db_cursor
deallocate db_cursor
select * from #TableSize
drop table #TableSize
-
did you create the proc on this server, and put it in the master database?
remember you also had to mark it as a system procedure with EXECUTE sp_ms_marksystemobject 'sp_spaceused2'
like i showe din the other thread.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
so it only needs to exist in master?
because at the moment it's in master and in one of other d/bs
use [master]go
select *
from sys.objects
where name = 'sp_spaceused2'
-
Well, you could try to use this procedure here[/url], instead. It will handle all of the DB switching and Dynamic SQL issues for you.
You could also try using this View here[/url], to get the table size information. However, its just for a single database, so you'd have to definie it in each database. OR, you could just extract the SELECT query and use it with the aforementioned OVER_SET procedure (above) ... 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
This works for me:
create table #TableSize (database_name varchar(150),
id int,
table_schema varchar(150),
table_name varchar(150),
num_rows int,
reserved varchar(150),
data varchar(150),
index_size varchar(150),
unused varchar(150)
)
INSERT INTO #TableSize
EXECUTE OVER_SET '
SELECT "{db}" As DB
, tbl.object_id As Tbl_ID
, sch.Name As [Schema]
, tbl.Name
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.total_pages)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [ReservedKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.total_pages - a.used_pages)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [UnusedKB]
FROM [{db}].sys.tables AS tbl
INNER JOIN [{db}].sys.schemas As sch ON sch.schema_id = tbl.schema_id
INNER JOIN [{db}].sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type="E")
',
@from = 'sys.sysdatabases WHERE dbid > 4',
@subs1 = '{db}=name',
@quote = '"'
;
SELECT * from #TableSize ;
DROP table #TableSize ;
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Thanks to all.
Here is what I ended up doing:
EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ''INSERT INTO #Tables EXEC sp_spaceused2 ''''&'''''',
@replacechar = ''&''
'
Eugene
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply