space_used_sp vs. sp_spaceUsed
Ok I'm not one to re-write standard system stored procedures for SQL Server. That being said I have been working a good deal with files and file groups within SQL Server over the past few days moving data and indexes to files and file groups other than the default PRIMARY file group. Sp_spaceUsed is great procedure for giving you overall storage utilization information for a database or database objects (table, index view, or queue). As I have been moving indexes and tables over to different file groups I wanted to see how this was splitting my data as far as size and row counts was going and how it was being disturbed across the file system. Unfortunately sp_spaceUsed does not give you this level of detail and I could not find a system view or other system procedure to give me all of the information that I wanted.
sp_spaceUsed
So I set out to create my own and now that I have it working and figured I'll share it with everyone. I ended up creating two objects, one a view sp_space_used_view and the second a procedure space_used_sp. I’m a database guy so I love organization so I put both of these objects into a schema named metadata that is owned by DBO. The view displays the space used by all objects in the database with rollup levels being displayed as ' -db-level', ' -schema-level', and ' -table-level'. The view relies upon the sys.objects (due to the fact that system tables are not in sys.tables so I was losing the space and row count information for these objects.), sys.indexes, sys.partions, sys.database_files, sys.schemas, sys.dm_db_partion_stats, sys.internal_tables, and sys.index_columns catalog views; all of which have detail coverage in BOL. The view utilizes these system catalog views to show rows counts, reserved space, data space, index space, and unused space in the database files broken down by table. I figure I would want to see all of the detailed data for a given database sometimes, rollups others, and individual objects separately as well so encapsulation of the select logic into a view allows re-use of this code in other objects. The procedure utilizes the view to show you information for a particular table or for the overall database. I hope this code is useful to others as well. One of the main differences between sp_spaceUsed and my space_used_sp is that sp_spaceUsed allows for the updating of system information which my procedure does not, it only returns data to the screen. The second difference is that sp_spaceUsed requires the schema name to be passed as part of the table name if you want to view information on an object outside of your default schema, space_used_sp does not require this and displays all objects with the name you provide giving you the schema name as part of the output.
space_used_sp vs. sp_spaceUsed
Here it is: (Note:everything is created in a schema call metadata)
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[space_used_vw]'))
DROP VIEW [metadata].[space_used_vw]
go
create view metadata.space_used_vw
-------------------------------------------
--SFIBICH
--7/7/2008
--The purpose of this view is to show space used broken
--down by SQL Server data files. Currently is does not calculate
--space for fulltext indexes and or XML indexes.
--
-------------------------------------------
as
select
top 100 PERCENT
coalesce([schema_name],' -db level-') schemaName
,coalesce(table_name,' -schema level-') tableName
,coalesce(data_file_name,' -table level-') dataFileName
,[rowCount]
,reserved*8 reservedKB
,data*8 dataKB
,case when used_page_count>data then (used_page_count-data)*8 else 0 end indexKB
,case when reserved>used_page_count then (reserved-used_page_count)*8 else 0 end unusedKB
from
(
SELECT
t.name table_name
,df.name data_file_name
,s.name [schema_name]
,SUM(
CASE
WHEN (p.index_id < 2) THEN row_count
ELSE 0
END
) [rowCount]
,SUM(reserved_page_count)+coalesce(max(IT_reserved_page_count),0) reserved
,SUM(
CASE
WHEN (p.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
)data
,SUM(used_page_count)+coalesce(max(IT_used_page_count),0) used_page_count
FROM sys.dm_db_partition_stats p
inner join
sys.objects t
on
p.object_id=t.object_id
inner join
sys.schemas s
on
t.schema_id=s.schema_id
inner join
sys.indexes i
on
p.object_id=i.object_id
and
p.index_id=i.index_id
inner join
sys.database_files df
on
i.data_space_id=df.data_space_id
left outer join
(--Internal tables are placed on the same filegroup as the parent entity. (BOL)
select
pp.object_id
,min(pp.index_id) index_id
,sum(p.reserved_page_count) IT_reserved_page_count
,sum(p.used_page_count) IT_used_page_count
FROM
sys.dm_db_partition_stats p
inner join
sys.internal_tables it
on
p.object_id = it.object_id
inner join
sys.dm_db_partition_stats pp
ON
it.parent_object_id=pp.object_id
WHERE
it.internal_type IN (202,204)
group by
pp.object_id
)it
on
p.object_id=it.object_id
and
p.index_id=it.index_id
group by
s.name
,t.name
,df.name
with rollup
) dataTable
order by
[schema_name]
,table_name
,[rowCount] desc
,data_file_name asc
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[metadata].[space_used_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [metadata].[space_used_sp]
go
create procedure metadata.space_used_sp
-------------------------------------------
--SFIBICH
--7/7/2008
--This is a replacement for sp_spaceused, it utlizes the view
--[metadata].[space_used_vw]. Currently it only reports on tables
--and the total for tables in a schema, or database level broken
--down by database files.
--
-------------------------------------------
@tableName sysname =null
as
if @tableName is null
begin
--------------------
--
--database, schema info
--
--------------------
select
' -db level-' schemaName
,' -schema level-' tableName
,dataFileName
,sum([rowCount]) [rowCount]
,sum(reservedKB) reservedKB
,sum(dataKB) dataKB
,sum(indexKB) indexKB
,sum(unusedKB) unusedKB
from
[metadata].[space_used_vw]
where
dataFileName <>' -table level-'
or
(
dataFileName =' -table level-'
and
schemaName=' -db level-'
and
tableName =' -schema level-'
)
group by
dataFileName
order by [rowCount] desc,dataFileName
end
else
begin
--------------------
--
--particular table
--
--------------------
select
schemaName
,tableName
,dataFileName
,[rowCount]
,reservedKB
,dataKB
,indexKB
,unusedKB
from
[metadata].[space_used_vw]
where
tableName
end
go