CODEWITHSUNDEEP
t-sql
Juri Bogdanov
Juri Bogdanov

Reputation: 295

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

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.

Upvotes: 0

Views: 840

Answers (4)

Juri Bogdanov
Juri Bogdanov

Reputation: 295

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

Upvotes: 0

KM.
KM.

Reputation: 103587

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.

Upvotes: 3

James Black
James Black

Reputation: 41858

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

Upvotes: 0

Chris Porter
Chris Porter

Reputation: 3687

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

Upvotes: 0

Related Questions