sp_msforeachdb

sp_msforeachdb

Post by <anonym.. » Fri, 20 Feb 2004 23:05:01



I've been trying to figure out a way to capture the
results of an sp_spaceused for every table in every
database on the server. I have a script that gets what I
need, but am having trouble putting it into a table. So I
was browsing through different sites looking for something
that would work and saw this example...


('?')"
which sounds almost exactly what I need. However when I
tried and modify it to us the sp_spaceused instead of the
dbcc, I received this error, please help


('?')"

Line 1: Incorrect syntax near 'Norhtwind_Rep'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Northwind'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'pubs'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'tempdb'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'testsb'.

 
 
 

sp_msforeachdb

Post by Anith Se » Fri, 20 Feb 2004 23:28:38


First, these are undocumented procedures which means these can be
unreliable, unsupported and can be removed or altered in a future version or
patch release. Second, these procedures internally use cursors, and maybe of
the same names and so, nesting of these procedures can be error-prone.

To get the used space info for all tables in a database:

EXEC sp_MSForEachTable 'sp_spaceused ''?'''

To get the used space info for all databases in a server:

EXEC sp_MSForEachDB 'sp_spaceused'

If you need information about all the tables in all the databases, the best
way is to roll out your own procedure, perhaps with a #temp table which can
hold information from each table for each database.

--
Anith

 
 
 

sp_msforeachdb

Post by Anith Se » Fri, 20 Feb 2004 23:33:24


Minor change: To get the used space info for all databases in a server:

EXEC sp_MSForEachDB '?..sp_spaceused '

--
Anith