SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


table sizes for all d/b on the server


table sizes for all d/b on the server

Author
Message
rightontarget
rightontarget
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 475
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
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75908 Visits: 41000
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!
rightontarget
rightontarget
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 475
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


Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75908 Visits: 41000
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!
rightontarget
rightontarget
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 475
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'



Attachments
Capture.PNG (75 views, 6.00 KB)
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36540 Visits: 9518
Well, you could try to use this procedure here, instead. It will handle all of the DB switching and Dynamic SQL issues for you.

You could also try using this View here, 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) ... :-D

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36540 Visits: 9518
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 ;



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
rightontarget
rightontarget
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 475
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search