sp_spaceused; Microsoft SQL Server Database and Implementation Training Kit

sp_spaceused; Microsoft SQL Server Database and Implementation Training Kit

Post by Brad Rust » Sat, 02 Sep 2000 01:37:02



Hi everyone.

In Chapter 9 of the Training Kit there is the following question:
Could you execute the sp_spaceused stored procedure against every table in a
specified database without the use of a Transact-SQL cursor?

The answer alludes to a solution but does not give it.  It states: 'You
could use a SELECT statement to create a loop and execute the sp_spaceused
stored procedure against each table.'

This is the one question for which I have no answer.
Could someone please show me a solution to avoid using cursors.

Thanks in advance,
Brad.

 
 
 

sp_spaceused; Microsoft SQL Server Database and Implementation Training Kit

Post by Roy Harve » Sat, 02 Sep 2000 06:46:57


Brad,

Quote:>In Chapter 9 of the Training Kit there is the following question:
>Could you execute the sp_spaceused stored procedure against every table in a
>specified database without the use of a Transact-SQL cursor?

>The answer alludes to a solution but does not give it.  It states: 'You
>could use a SELECT statement to create a loop and execute the sp_spaceused
>stored procedure against each table.'

While the book is not incorrect, the only solution that comes to mind
is bad enough that I would prefer to think that they made a mistake
when they said it rather than intend something like:





BEGIN




END

Roy

 
 
 

sp_spaceused; Microsoft SQL Server Database and Implementation Training Kit

Post by Erland Sommarsk » Thu, 07 Sep 2000 06:53:20



>In Chapter 9 of the Training Kit there is the following question: Could
>you execute the sp_spaceused stored procedure against every table in a
>specified database without the use of a Transact-SQL cursor?

>The answer alludes to a solution but does not give it.  It states: 'You
>could use a SELECT statement to create a loop and execute the sp_spaceused
>stored procedure against each table.'

>This is the one question for which I have no answer.
>Could someone please show me a solution to avoid using cursors.

One solution is to use sp_MSforeachtable, but I doubt that this the
answer they are thinking of, since that one is not in Books Online,
at least not for SQL2000 (so I wouldn't expect to be in earlier
versions either).

And peeking to the code this SP I find that it uses - you guessed it -
a cursor.

Apart from that there is the solution that Roy Harvey offered, which
in this case is more of a kludge than using a cursor. But there are
situations where iterations comes out more convenient with loop
without SELECT MIN, rather than a cursor.

--