August 22, 2012 at 3:06 pm
Hi all,
I am new to sql server. I need to log table sizes to report on growth. Here is what I am using to do that:
create table #TableSize (name varchar(150),
rows int,
reserved varchar(150),
data varchar(150),
index_size varchar(150),
unused varchar(150)
)
insert into #TableSize
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''
select .......
drop table #TableSize
I noticed that there is no schema in the result of the sp_spaceused, so I end up with 3 tables of the same name and I have no idea what schema they belong to.
I need somehow get the schema. Anything I can do in what I already have to get it?
Thanks,
Eugene
August 22, 2012 at 3:21 pm
There are some scripts here that might help you: http://www.sqlservercentral.com/search/?q=space+for+all+tables&t=a&t=s
The other thing you might do is take sp_spaceused, get the code and add schema to it.
August 22, 2012 at 3:34 pm
Thanks for reply. I like the idea of adding the schema to what I already have. You wrote:
<quote>The other thing you might do is take sp_spaceused, get the code and add schema to it.<end quote>
Can you elaborate please?
August 23, 2012 at 5:45 am
eugene.pipko (8/22/2012)
Thanks for reply. I like the idea of adding the schema to what I already have. You wrote:<quote>The other thing you might do is take sp_spaceused, get the code and add schema to it.<end quote>
Can you elaborate please?
EXEC sp_spaceused2 '[Orange].[tblBananas]'
schemanamenamerowsreserveddataindex_sizeunused
ORANGEtblBananas0 0 KB0 KB0 KB0 KB
here's an example of what Steve Jones is talking about; i simply added teh schema name to the results. note you also need to mark this a s a system object as well.
create procedure dbo.sp_spaceused2 --- 2003/05/19 14:00
@objname nvarchar(776) = null, -- The object we want size on.
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
as
declare @id int -- The object id that takes up space
,@schemaname sysname
,@type character(2) -- The object type.
,@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
/*
** Try to find the object.
*/
SELECT @id = object_id, @schemaname=SCHEMA_NAME(schema_id), @type = type FROM sys.objects WHERE object_id = object_id(@objname)
-- Translate @id to internal-table for queue
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
/*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
-- Is it a table, view or queue?
IF @type NOT IN ('U ','S ','V ','SQ','IT')
begin
raiserror(15234,-1,-1)
return (1)
end
end
/*
** Update usages if user specified to do so.
*/
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
/*
** If @id is null, then we want summary data.
*/
if @id is null
begin
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles
select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
/* unallocated space could not be negative */
select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB'),
'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2) + ' MB')
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(data_pages) + sum(text_used)
** index: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
select
reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end
/*
** We want a particular object.
*/
else
begin
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;
/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don't
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END
SELECT
schemaname = @schemaname,
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
end
return (0) -- sp_spaceused2
GO
--make this a sys proc equivilent
--required if the proc does not exist int he database you are testing
--we want one copy in master to work everywhere
EXECUTE sp_ms_marksystemobject 'sp_spaceused2'
Lowell
August 23, 2012 at 8:05 am
Lowell has done it for you, but all of the stored procedures Microsoft provides are just normal coded stored procedures. You can view the code in SSMS and modify it, creating your own procedure. Just as you would for any other code you compile in SQL Server.
Make sure you source control it, and document your changes for DR purposes. If you have to redeploy this on a new server, you want to have the same consistency.
August 23, 2012 at 10:31 am
Thank you both for your help. Learned something new.
I didn't even think of a possibility of looking into sys object's code as I came from Oracle where it is not possible.
Thanks again,
Eugene
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply