Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Script Question - sp_MSforeachdb/sp_MSforeachtable/sp_mstablespace

Status
Not open for further replies.

DBAWinnipeg

Programmer
Apr 14, 2004
173
CA
Does anyone have a script that will go through each DB

sp_MSforeachdb

Every Table

sp_MSforeachtable

and execute

sp_mstablespace

??



Thanks in advance!!!

Colin in da 'Peg :)
 
Code:
sp_MSforeachdb 'use ?
DECLARE @str nvarchar(100)
SET @str = N''exec sp_MSforeachtable ''''print ''''''''[?].''+char(63)+'''''''''' exec sp_mstablespace ''''''''''+char(63)+''''''''''''''''
PRINT @str'

Then take the output of this, paste it into the Query Analyzer window, and run it.

You'll have to correlate the returned recordsets with the printed names.

Doing EXEC (@str) does the work, but you don't see any results, because it's in a different context (I think).

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k
 
Btw. is it possible to call eachtable directly from eachdb at all? AFAIK both use the same worker cursor.
 
That could be the problem...

How do you like the string of sixteen quote marks? [smile]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top