sp_spaceused for all databases (in 6.5)

Question

Post reply

sp_spaceused for all databases (in 6.5)

Rootman

SSCrazy

Points: 2641
More actions
September 18, 2002 at 6:11 am

#173341

Create table #SQLOLEDbUserProfile
(dbid int NOT NULL PRIMARY KEY,
profilebits int NOT NULL)
EXEC sp_MSdbuserprofile 'init'
Hi,
I am trying to create a job which will run nightly to show database info for a server. I want to include the info you get when you run sp_spaceused, but for all of the dbs. The code below doesn't work, as the job runs in master, it just give the info for master as many times as I have got dbs. Does anyone know a way?
EXEC sp_Msforeachdb "EXEC sp_spaceused"
DROP TABLE #SQLOLEDbUserProfile

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rootman SSCrazy Points: 2641 More actions · Answer 1

Sorry I overtyped and split the code - here it is as it should read:

Hi,

I am trying to create a job which will run nightly to show database info for a server. I want to include the info you get when you run sp_spaceused, but for all of the dbs. The code below doesn't work, as the job runs in master, it just give the info for master as many times as I have got dbs. Does anyone know a way?

Create table #SQLOLEDbUserProfile

(dbid int NOT NULL PRIMARY KEY,

profilebits int NOT NULL)

EXEC sp_MSdbuserprofile 'init'

EXEC sp_Msforeachdb "EXEC sp_spaceused"

DROP TABLE #SQLOLEDbUserProfile

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

If you run:

EXEC sp_Msforeachdb "EXEC sp_spaceused"

You get the output for the same datbase over and over again, since sp_spaceused runs in the context of the current database. Now if you want this to work for each database you need to have it read:

EXEC sp_Msforeachdb 'EXEC "?"..sp_spaceused'

The "?" will be replaced with the current database for each executiong fo sp_Msforeachdb.

Hope this is your problem....

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Rootman SSCrazy Points: 2641 More actions · Answer 3

Hi Greg,

Thanks for your reply.

Your code works perfectly in 7, however, the same code in 6.5 produces this error:

Line 1: Incorrect syntax near 'master'.

I get this for each db on the server!!

Maybe there is another way????

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Since I don't have 6.5 I really don't know what your problem might be. I would suggest you review sp_Msforeachdb code to determine exactly what the command is that gets executed.

Possibly you might try to this:

EXEC sp_Msforeachdb 'EXEC "?".dbo.sp_spaceused'

or something like this

declare @cmd char(200)

set @cmd = 'use "?"' + char(13) + 'EXEC sp_spaceused'

exec sp_Msforeachdb @cmd

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP