July 28, 2009 at 10:29 am
Hi Folks
can someone tell me if there is a way to just select certain attributes from sp_spaceused
I am trying this out as an example:
USE AdventureWorks;
GO
EXEC sp_spaceused @updateusage = N'True';
GO
it gives me all the columns and data
but I would like to be able to just select the database_name,database_size, and maybe unused as an example
is that possible or do I need to look somewhere else to get that info
Thanks
Jim
July 28, 2009 at 11:07 am
You can view the code for the sp_spaceused procedure in the master database. You can't change it but you could write you own procedure for just that data you are looking for.
July 28, 2009 at 12:34 pm
Thanks Matt
Jim
July 28, 2009 at 3:32 pm
Take a look at sys.dm_db_partition_stats. It details storage consumed by each partition in the database (unpartitioned tables and indexes exist as a single partition each), with index space detailed separately from the base table space.
All values displayed are in SQL Server data pages, which are 8KB each, so you'll need to multiply the values by 8192 to convert from pages to bytes.
The object_id and index_id columns may be joined to sys.objects (on object_id) and sys.indexes (on object_id and index_id) for further info.
For example, this will provide detail on the entire database:
SELECT DB_NAME(), SUM(rows) as Rows, SUM([Data MB]) AS [Data MB], SUM([LOB MB]) AS [LOB MB],
SUM([Used MB]) AS [Used MB], SUM([Reserved MB]) AS [Reserved MB],
SUM([Reserved MB]) - SUM([Used MB]) AS [Unused MB]
FROM (
SELECT sum(CASE WHEN index_id < 2 THEN row_count ELSE 0 END) AS rows,
(sum(in_row_used_page_count) * 8.0) / 1024 AS [Data MB],
(sum(lob_used_page_count) * 8.0) / 1024 AS [LOB MB],
(sum(used_page_count) * 8.0) / 1024 AS [Used MB],
(sum(reserved_page_count) * 8.0) / 1024 AS [Reserved MB]
FROM sys.dm_db_partition_stats s
GROUP BY OBJECT_ID
) a
GO
...and this will provide detail by table:
SELECT object_name(s.object_id) as [Table],
sum(CASE WHEN index_id < 2 THEN row_count ELSE 0 END) AS rows,
(sum(in_row_used_page_count) * 8.0) / 1024 AS [Data MB],
(sum(lob_used_page_count) * 8.0) / 1024 AS [LOB MB],
(sum(used_page_count) * 8.0) / 1024 AS [Used MB],
(sum(reserved_page_count) * 8.0) / 1024 AS [Reserved MB],
((sum(reserved_page_count) - sum(used_page_count))* 8.0) / 1024 AS [Unused MB]
FROM sys.dm_db_partition_stats s
-- Uncomment the WHERE clause and include a table name to view info on a single table
-- WHERE object_id = OBJECT_ID('Put table name here')
GROUP BY object_id
-Eddie
Eddie Wuerch
MCM: SQL
July 29, 2009 at 9:43 am
Hi Eddie
Thanks very much for sharing your code
I will try these out and see what it gives me
Appreciate Your TIme
Jim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply