Msforeachtable

Question

Post reply

Msforeachtable

SA-1

SSCrazy Eights

Points: 8113
More actions
April 20, 2006 at 9:28 am

#168648

Is there a way to insert the output from the sp_msforeachtable proc in to a table.
the query i am trying to run is :sp_MSforeachtable 'sp_spaceused ''?'''
I tried the different variations of the insert statement as well but that didn't help as well.
Any ideas!!!
TIA

Viewing 7 posts - 1 through 6 (of 6 total)

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

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 1

hi,

you can try the following :-

create table #test

(name varchar(100), rows int, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))

insert

into #test

exec ( "sp_MSforeachtable 'sp_spaceused ''?'''")

select * from #test

this works, you might need to tidy it up a bit

hope this helps

paul

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 2

your right...

this is the script that worked for me...

CREATE TABLE #tblTableData

(

vchTableName VARCHAR(100) NOT NULL,

intRows INT NOT NULL,

vchReservedSpace VARCHAR(100) NOT NULL,

vchData VARCHAR(100) NOT NULL,

vchIndexSize VARCHAR(100) NOT NULL,

vchUnusedSpace VARCHAR(100) NOT NULL

&nbsp

INSERT INTO #tblTableData

EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT * FROM #tblTableData

DROP TABLE #tblTableData

On using table variable I got the an execute error.

Thanks anyways...

ram302 SSCommitted Points: 1618 More actions · Answer 3

I know this is an old post, but how does this work? I have tried the following:

EXECUTE sp_MSforeachtable

'EXECUTE master.dbo.xp_cmdshell ''osql -E -S "MYSQLSERVER" -d "MyDB" -q "sp_help ''?''" ' ' '

It complains about using [dbo]., but I even tried adding a replace and then removing the single quotes surrounding the ?, to no avail.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

ram302 (4/7/2016)
I know this is an old post, but how does this work? I have tried the following:
EXECUTE sp_MSforeachtable
'EXECUTE master.dbo.xp_cmdshell ''osql -E -S "MYSQLSERVER" -d "MyDB" -q "sp_help ''?''" ' ' '
It complains about using [dbo]., but I even tried adding a replace and then removing the single quotes surrounding the ?, to no avail.

Why are you calling xp_cmdshell to call osql? Why won't you query directly the server?

By the way, it seems that your loop is in the wrong place.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Yes, it is an old post. Far better to start a new topic.

Luis is right - it doesn't make sense to shell out to a command line and then connect back to SQL Server. The output is almost completely unintelligible, and when you want to run it on a different server, you have to change the script. But, to answer your question, all you need to do is add a couple more single quotes, thus:

EXECUTE sp_MSforeachtable

'EXECUTE master.dbo.xp_cmdshell ''osql -E -S "." -d "master" -q "EXEC sp_help ''''?''''" '' '

John

ram302 SSCommitted Points: 1618 More actions · Answer 6

Thanks, John! And I understand your point, but I had to output table stats to pipe delimited files. Thanks again, I wish I could give +1 for this one.