How can I convert EXEC operation to Table variable for further SELECT?

Question

I have the next query that I run via SQLCMD.EXE

use [AxDWH_Central_Reporting]
GO
EXEC sp_spaceused @updateusage = N'TRUE'
GO

That return 2 tables... and uglu output file with the next conent:

Changed database context to 'AxDWH_Central_Reporting'.

database_name                                                                                                                   Pdatabase_size     Punallocated space 
--------------------------------------------------------------------------------------------------------------------------------P------------------P------------------
AxDWH_Central_Reporting                                                                                                         P10485.69 MB       P7436.85 MB        
reserved          Pdata              Pindex_size        Punused            
------------------P------------------P------------------P------------------
3121176 KB        P3111728 KB        P7744 KB           P1704 KB           
----------------------------------------------------------------

Is it possible to make it shorter? Exactly I need only database_name and database_size values.. I tryed SQL queryes like

SELECT database_name, database_size FROM (EXEC sp_spaceused @updateusage = N'TRUE') AS tbl1

but this is not working.

Answer 1

Convert your stored proc to a Table-Valued User-Defined Function

Answer 2

If you have to use a stored procedure then you can insert that into a table variable, and then do a select from there to get just the information that you need.

The other option, if you have control over the stored procedure is to pass in a select parameter, as I show below. If you can't do what I called @SelectClause, then once you insert into the table you can do a simple select.

INSERT INTO @atttable (RowID, Name, ID, AttributePosition, AVTable, KeyField, EntityNameField, Virtual, DataType, AttributeListName, AttributeRequired, AttributeUnique) 
 EXEC [dbo].[SomeStoredProcedure] 
 @SelectClause='ROW_NUMBER() OVER(ORDER BY AttributePosition) RowID, AttributeName, AttributeID, AttributePosition, EntityAVTableName, EntityKeyField, EntityNameField, Virtual, AttributeDataType, AttributeListName, AttributeRequired, AttributeUnique', 
 @WhereClause=@EntityWhereClause

Answer 3

sp_spaceused is a system stored procedure. You can view the source code in management studio in the master database. Copy the code into your own procedure, modify it to return only what you need.

Answer 4

I opened sp_spaceused system stored procedure and did SELECTs which i need :)

declare
    @dbname sysname,
    @dbsize bigint,
    @logsize bigint,
    @reservedpages  bigint

select
    @reservedpages = sum(a.total_pages)
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

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 
    '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')