August 27, 2007 at 3:51 am
Hi,
I currently have a script that gets the table sizes of all the tables in a db. When I run the script on each database it bring back results. I am using sp_foreachtable in the script. Problem is I want the script to bring back the results from all the databases not just one. I tried using sp_foreachdb with the sp_foreachtable but that won't work. Does anyone perhaps have an idea what I need to do to bring back the results from all databases.
My script looks like this:
create PROCEDURE [dbo].[sp__TableSize1]
AS
SET NOCOUNT ON
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
total_sizeg decimal(15,2),
percent_of_db decimal(15,2),
db_size decimal(15,2),
db_sizeg decimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_MSforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_MStablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
--Exec sp_MSforeachdb
/*EXEC sp_MSforeachtable
@command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_MStablespace '?'",
@command2="update #temp set table_name = '?'
from sysdatabases
where rec_id = (select max(rec_id) from #temp)" */
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Convert total_size to GIG
UPDATE #temp
SET total_sizeg = total_size /1048000
-- Convert db_size to GIG
UPDATE #temp
SET db_sizeg = db_size /1048000
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT @@ServerName as ServerName, db_name() as DatabaseName, table_name as TableName, nbr_of_rows as [Rows],
data_space as DataKB, index_space as IndexKB,
total_size as TotalSizeKB,
total_sizeg as TotalSizeGB,
percent_of_db as [%Usage],
db_size as DBSizeKB, db_sizeg as DBSizeGB,
(convert(varchar(8),getdate(),112)) as RunDate
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
GO
August 27, 2007 at 6:01 am
This is done usually in a separate database where you run exclusively administration tasks, lets name it DBAdmin; you create a table in this db that will contain all your sqlservers / database names where you want to run the script above "for each table"; now just create a cursor on that table and run the script for each of its record, which means for each database you want the script to be run against.
April 15, 2008 at 5:34 pm
Hi - I'm facing the same situation, the problem seems to be that you can't pass a database parameter to the sp_foreachtable proc (also, the USE database GO syntax doesn't seem to work with sp_executesql), I'll go ahead and code this up manually using pseudo cursors with four part naming convention names for the tables and post the script in the next day or two.
August 25, 2010 at 7:26 pm
Here is one of my favorite script that does what you need for one database at a time with a preceeding USE dbname... So if you want to do this for all database then wrap this inside a cursor... However run it first and see the difference in the two results so you can choose the one you want... Pay attention to the note of DBCC UpdateUsage, very important.
Hank Freeman
end.
Here is the script .. 🙂
/*** This is the script to obtain table row and size counts and output them to two results
File name: SCRIPT_Stable_Size_Info(2000)sql
SS-2000 can not use @Tables ... not yet implemented *
***/
go
--DBCC UPDATEUSAGE(0)
go
/*** This is the script to obtain table row and size counts and output them to two results
File name: SCRIPT_Stable_Size_Info.sql
***/
SET NOCOUNT ON
Declare @TableName sysname
, @var_SERVER_NAME VARCHAR(25)
, @var_DBName nvarchar(50)
SET @var_SERVER_NAME = (SELECT CONVERT(VARchar(25), SERVERPROPERTY('servername')))
set @var_DBName = 'GASKEY'
create table #Tables
(
ID Int Identity,
TableName sysname null,
NoOfRows BigInt,
reserved_kb varchar(20) null,
data_kb varchar(20) null,
index_size_kb varchar(20) null,
unused_kb varchar(20) null
)
/**
--DBCC UPDATEUSAGE(0) --Please reference this page for explanatiOn of negative values for your need to run this command first
http://sqlserver2000.databases.aspfaq.com/why-does-sp-spaceused-return-inaccurate-values.html
**/
declare c cursor for
Select Name from sysobjects where xtype ='U' order by name asc -- User tables Only
open c
fetch Next from c into @TableName
while @@Fetch_Status =0
begin
Insert #Tables(Tablename,NoOfRows,reserved_kb,data_kb,index_size_kb,unused_kb) Exec sp_spaceused @TableName
-- Update @Tables Set TableName = @TableName Where ID = @@Identity
fetch Next from c into @TableName
end
close c
deallocate c
select * , getdate() as TimeStamp from #Tables Order By NoOfRows DESC ,Tablename ASC
--SUBSTRING ( value_expression ,start_expression , length_expression )
select id, @var_SERVER_NAME AS 'Server_Name',@var_DBName as 'DatabaseName'
,TableName, NoOfRows,
--CAST ( expression AS data_type [ (length ) ])
cast(substring(reserved_kb,1,(len(reserved_kb)-3)) as bigint) as 'reserved_kb',
cast(substring(data_kb,1,(len(data_kb)-3)) as bigint) as 'data_kb',
cast(substring(index_size_kb,1,(len(index_size_kb)-3)) as bigint) as 'index_size_kb',
cast(substring(unused_kb,1,(len(unused_kb)-3)) as bigint) as 'unused_kb',
getdate() as 'TimeStamp',
cast(datepart(yyyy,getdate()) as varchar(4))
+
case len(cast(datepart(mm,getdate()) as varchar(4)))
when '1' then '0' + cast(datepart(mm,getdate()) as varchar(4))
else cast(datepart(mm,getdate()) as varchar(4))
end
+
case len(cast(datepart(dd,getdate()) as varchar(4)) )
when '1' then '0' + cast(datepart(dd,getdate()) as varchar(4))
else cast(datepart(dd,getdate()) as varchar(4))
end
+
case len(cast(datepart(hh,getdate()) as varchar(4)) )
when '1' then '0' + cast(datepart(hh,getdate()) as varchar(4))
else cast(datepart(hh,getdate()) as varchar(4))
end
+
case len(cast(datepart(mi,getdate()) as varchar(4)) )
when '1' then '0' + cast(datepart(mi,getdate()) as varchar(4))
else cast(datepart(mi,getdate()) as varchar(4))
end
+
case len(cast(datepart(ss,getdate()) as varchar(4)) )
when '1' then '0' + cast(datepart(ss,getdate()) as varchar(4))
else cast(datepart(ss,getdate()) as varchar(4))
end as TimeStamp_Numb_Str
from #Tables Order By NoOfRows DESC ,Tablename ASC
SET NOCOUNT OFF
drop table #Tables
/***** end ****/
Hank Freeman
Senior SQL Server DBA / Data & Solutions Architect
hfreeman@msn.com
678-414-0090 (Personal Cell)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply