Export the "Object Explorer Details" for tables

[djj55]

Hello, SQL 2008R2
What I would like is a way to save the information that can be shown in the Object Explorer Details tab when you are in Object Explorer and have selected Tables under a database.
I currently show Name, Row Count, Create Date, Data Space Used (KB), and Schema.

I have

SELECT 
    t.name AS TableName
    , SCHEMA_NAME(t.schema_id) AS schema_name
    , i.rows
    , t.create_date 
FROM sys.tables AS t 
INNER JOIN sys.sysindexes AS i 
ON t.object_id = i.id 
    AND i.indid < 2

which shows me everything except size. Since sys.sysindexes has page information is there a way to get from that to size? Or is there another system table I can use? I now about sp_spaceused but would rather use a single query rather than create a stored procedure.

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

There is a system stored procedure named sp_spaceused that can return this information. Most people don't realize this, but you can actually get the code that SQL runs when you call the procedure.

For example, if you want to see the data space used for a table...

[tt]sp_spaceused 'TableNameHere'[/tt]

This actually shows the reserved space, data space, index size and unused space.

My suggestion is to look at the code in the sp_spaceused procedure and figure out how to use this code within your own query. To see the code for a stored procedure:

sp_helptext 'sp_spaceused'

It helps if you set the output to text. Now you can copy/paste the code to a new query window and start modifying it to suit your needs. Just make sure you remove the Create Procedure part.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For what it's worth, looks like you can multiply dpages by 8 to get the size (in kilobytes) for the table.

SELECT 
    t.name AS TableName
    , SCHEMA_NAME(t.schema_id) AS schema_name
    , i.rows
    , t.create_date
    , Convert(varchar(10), dpages * 8) + ' KB' As DataSize
FROM sys.tables AS t 
INNER JOIN sys.sysindexes AS i 
ON t.object_id = i.id 
    AND i.indid < 2

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thanks George. As I stated I knew about sp_spaceused but your suggestion for sp_helptext did the trick as I found that I could use

convert(dec(15,2),i.dpages) * 8192 / 1048576

The whole code in sp_spaceused is

database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),logsize)) * 8192 / 1048576,15,2) + ' MB')

so I have to look up where the logsize is located.

Thanks again,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Well after looking at their code I find I need to work on logic as they use different sources.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Here is what I came up with

;
WITH Size_CTE (SumPage, ObjectName) 
AS ( 
SELECT   
	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  
		) AS SubPage 
	, OBJECT_NAME(object_id) AS ObjectName 
FROM sys.dm_db_partition_stats  
GROUP BY OBJECT_NAME(object_id)
)
SELECT 
    t.name AS TableName
    , SCHEMA_NAME(t.schema_id) AS schema_name
    , i.rows
    , t.create_date 
    , s.SumPage * 8 
FROM sys.tables AS t 
INNER JOIN sys.sysindexes AS i 
ON t.object_id = i.id 
    AND i.indid < 2 
INNER JOIN Size_CTE s 
ON t.name = s.ObjectName 
ORDER BY TableName

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!