Sunday, February 12, 2012

Can I output the result of sp_spaceused to a table ?

Firstly, I created a table : test01 as follow :
Field Data type
db_name char (80)
db_size char (20)
unallocated_space char (20)
reserved char (20)
data char (20)
index_size char (20)
unused char (20)
Then, I executed this command in SQL Analyzer :
INSERT INTO test01 EXEC sp_spaceused
But I only got this result :
Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
Insert Error: Column name or number of supplied values does not match table
definition
Can I output the result of : EXEC sp_spaceused to a table ?
Was my SQL statement correct or incorrect ?
sp_spaceused has two result sets, so no you can't do that directly...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c8t0tc$91f2@.imsp212.netvigator.com...
> Firstly, I created a table : test01 as follow :
> Field Data type
> db_name char (80)
> db_size char (20)
> unallocated_space char (20)
> reserved char (20)
> data char (20)
> index_size char (20)
> unused char (20)
> Then, I executed this command in SQL Analyzer :
> INSERT INTO test01 EXEC sp_spaceused
> But I only got this result :
> Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> Insert Error: Column name or number of supplied values does not match
table
> definition
>
> Can I output the result of : EXEC sp_spaceused to a table ?
> Was my SQL statement correct or incorrect ?
>
>
|||Try looking at the code in master..sp_spaceused. You could probably pick
out the portions into separate procs. Would loose the features of new
sp_spaceused during a SQL Server version upgrade but if you are desperate
enough this is an option.
-Paritosh
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eoF5B4ZQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> sp_spaceused has two result sets, so no you can't do that directly...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c8t0tc$91f2@.imsp212.netvigator.com...
> table
>
|||> Would loose the features of new
> sp_spaceused during a SQL Server version upgrade but if you are desperate
> enough this is an option.
Right, but to be honest, that is slightly safer than relying on the sp_ to
not change... since if you *could* do insert #table exec sp_spaceused and
the resultset changes, suddenly your code breaks. If your code is merely
*based on* the sp_ then it won't break when the sp_ changes.
Then again, your code would likely rely on calls to system tables such as
sysindexes etc. which are going to change and which will eventually
disappear, so neither method would be truly safe...
A

No comments:

Post a Comment