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 ?
>
>|||You can do that at a table level:
Example for Northwind Employees table.
Create Table #Temp1
(Tablename CHAR(80),
rows int,
un_space VARCHAR(50),
reserved VARCHAR(50),
data VARCHAR(50),
index_size VARCHAR(50))
GO
Insert into #Temp1
Exec sp_spaceused 'Employees'
GO
Select * from #Temp1
GO
Drop table #Temp1
GO
>--Original Message--
>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...
> > 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 ?
> >
> >
> >
>|||> 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|||Thanks to everybodies.
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:115ac01c441a1$917301b0$a001280a@.phx.gbl...
> You can do that at a table level:
> Example for Northwind Employees table.
>
> Create Table #Temp1
> (Tablename CHAR(80),
> rows int,
> un_space VARCHAR(50),
> reserved VARCHAR(50),
> data VARCHAR(50),
> index_size VARCHAR(50))
> GO
> Insert into #Temp1
> Exec sp_spaceused 'Employees'
> GO
> Select * from #Temp1
> GO
> Drop table #Temp1
> GO
>
> >--Original Message--
> >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 ?
> >
> >
> >
> >.
> >