Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

table sizes for all d/b on the server Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 2:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 AM
Points: 100, Visits: 333
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
Post #1349359
Posted Thursday, August 23, 2012 2:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349363
Posted Thursday, August 23, 2012 3:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 AM
Points: 100, Visits: 333
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

Post #1349367
Posted Thursday, August 23, 2012 3:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349368
Posted Thursday, August 23, 2012 3:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 AM
Points: 100, Visits: 333
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'



  Post Attachments 
Capture.PNG (65 views, 6.79 KB)
Post #1349371
Posted Thursday, August 23, 2012 3:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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) ...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1349376
Posted Thursday, August 23, 2012 4:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #1349387
Posted Monday, August 27, 2012 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 AM
Points: 100, Visits: 333
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
Post #1350443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse