StorProc Help

[Davy]

Hi All,

I am trying to report the freespace on ALL database in a server (as can be obtained from one database using sp_spaceused). I have adapted the sp_spaceused procedure to give me the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database name> <value> <value>

I have created a cursor that takes the database name values from sp_databases and then uses the value from the cursor as a variable when executing the sp_spaceused stored proc. To execute in the stored proc this requires the &#39;USE <database> EXEC master..sp_spaceused.

This gives me the correct information but in the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database 1 name> <value> <value>

database_name database_size unallocated_space
----------------------------------------------------------
<database 2 name> <value> <value>

database_name database_size unallocated_space
----------------------------------------------------------
<database 3 name> <value> <value>

I am looking for it in the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database 1 name> <value> <value>
<database 2 name> <value> <value>
<database 3 name> <value> <value>

I tried to output the results of the sp_spaceused (using the curosr variable) into a temporary table to list the results properly, but the query will not allow a &#39; select @variable <SQL Statement>&#39; with an insert into temp table command!!!

[WizKid]

try this..

create table tabl_x (db_name varchar(30), db_size int, unallo_sp int)
:
:
open cursor...
loop:
:
insert into tabl_x
fetch cursor
:
:
end_of_loop
close cursor
:

select * from tabl_x


------------
Davy at 7/18/01 9:47:16 AM

Hi All,

I am trying to report the freespace on ALL database in a server (as can be obtained from one database using sp_spaceused). I have adapted the sp_spaceused procedure to give me the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database name> <value> <value>

I have created a cursor that takes the database name values from sp_databases and then uses the value from the cursor as a variable when executing the sp_spaceused stored proc. To execute in the stored proc this requires the &#39;USE <database> EXEC master..sp_spaceused.

This gives me the correct information but in the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database 1 name> <value> <value>

database_name database_size unallocated_space
----------------------------------------------------------
<database 2 name> <value> <value>

database_name database_size unallocated_space
----------------------------------------------------------
<database 3 name> <value> <value>

I am looking for it in the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database 1 name> <value> <value>
<database 2 name> <value> <value>
<database 3 name> <value> <value>

I tried to output the results of the sp_spaceused (using the curosr variable) into a temporary table to list the results properly, but the query will not allow a &#39; select @variable <SQL Statement>&#39; with an insert into temp table command!!!

[Davy]

The following is the query:

CREATE TABLE #fixeddrives (dbname char(20), dbsize char(17), dbfree varchar(17))
DECLARE @dbase varchar(30)
declare @SQL varchar (170)
set nocount on
DECLARE Dnames_cursor CURSOR

FOR SELECT name FROM sysdatabases
OPEN Dnames_cursor
FETCH NEXT FROM Dnames_cursor INTO @dbase
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
select @SQL = &#39;use &#39; + @dbase + &#39; exec sp_space&#39;
EXEC(@SQL)
END
FETCH NEXT FROM Dnames_cursor INTO @dbase
END
DEALLOCATE Dnames_cursor
drop table #fixeddrives

IT CALLS THE SP_SPACE STORED PROCEDURE THAT DELIVERS THE FOLLOWING OUTPUT:

database_name database_size unallocated space
--------------- ------------------ ------------------
Northwind 10.94 MB 7.95 MB

THEN THE CURSOR MOVES ONTO THE NEXT DATABASE. I WANT TO PUT THE ABOVE OUTPUT INTO THE TEMP TABLE...


------------
WizKid at 7/18/01 11:47:34 AM

try this..

create table tabl_x (db_name varchar(30), db_size int, unallo_sp int)
:
:
open cursor...
loop:
:
insert into tabl_x
fetch cursor
:
:
end_of_loop
close cursor
:

select * from tabl_x


------------
Davy at 7/18/01 9:47:16 AM

Hi All,

I am trying to report the freespace on ALL database in a server (as can be obtained from one database using sp_spaceused). I have adapted the sp_spaceused procedure to give me the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database name> <value> <value>

I have created a cursor that takes the database name values from sp_databases and then uses the value from the cursor as a variable when executing the sp_spaceused stored proc. To execute in the stored proc this requires the &#39;USE <database> EXEC master..sp_spaceused.

This gives me the correct information but in the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database 1 name> <value> <value>

database_name database_size unallocated_space
----------------------------------------------------------
<database 2 name> <value> <value>

database_name database_size unallocated_space
----------------------------------------------------------
<database 3 name> <value> <value>

I am looking for it in the following output:

database_name database_size unallocated_space
----------------------------------------------------------
<database 1 name> <value> <value>
<database 2 name> <value> <value>
<database 3 name> <value> <value>

I tried to output the results of the sp_spaceused (using the curosr variable) into a temporary table to list the results properly, but the query will not allow a &#39; select @variable <SQL Statement>&#39; with an insert into temp table command!!!