November 30, 2009 at 9:55 am
Thanks for the article. I've used a number of these stored procedures before but a couple were new to me.
One interesting point that you may or may not know about sp_MSforeachtable is that you can specify a where condition for it to limit the number of tables you operate against.
For instance, if all of your audit tables ended with the word AUDIT you could truncate only that group by using the following command.
--truncate all AUDIT tables
exec sp_MSforeachtable @command1 = "truncate table ? ", @whereand = "and o.name like '%AUDIT' "
It is important to specify the 'o' in 'o.name' as that is the underlying alias for the sys.objects table in the stored procedure.
November 30, 2009 at 11:13 pm
atkinsd (11/30/2009)
Thanks for the article. I've used a number of these stored procedures before but a couple were new to me.One interesting point that you may or may not know about sp_MSforeachtable is that you can specify a where condition for it to limit the number of tables you operate against.
For instance, if all of your audit tables ended with the word AUDIT you could truncate only that group by using the following command.
--truncate all AUDIT tables
exec sp_MSforeachtable @command1 = "truncate table ? ", @whereand = "and o.name like '%AUDIT' "
It is important to specify the 'o' in 'o.name' as that is the underlying alias for the sys.objects table in the stored procedure.
That is excellent. Thank you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
January 6, 2010 at 10:01 am
Timothy,
when attempting to run sp_msforeachtable in each of the variations below, I get these errors. I have tried running from both the master and CBL databases, with all same results.
Any Ideas?
exec dbo.sp_msforeachtable 'select * from CBL.dbo.[?]'
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.
exec dbo.sp_msforeachtable 'select * from CBL.[?]'
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.[dbo].[ae_amap]'.
exec dbo.sp_msforeachtable 'select * from [?]'
Msg 208, Level 16, State 1, Line 1
Invalid object name '[dbo].[ae_amap]'.
exec dbo.sp_msforeachtable 'select * from [CBL].dbo.[?]'
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.
exec dbo.sp_msforeachtable 'select * from [CBL].[dbo].[?]'
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.
Any help from anyone will be much appreciated.
January 6, 2010 at 10:30 am
Probably a daft question but you are running the proc in the correct database?
January 6, 2010 at 10:47 am
David.Poole (1/6/2010)
Probably a daft question but you are running the proc in the correct database?
I have tried running from both the master and CBL databases, with all same results. (edited original post to reflect same)
Actually, when I run from master, it returns tables from another database, not the specified "CBL" database.
January 6, 2010 at 11:02 am
You are going to just love this one! Try it like this:exec dbo.sp_msforeachtable 'select * from ?'
Note the lack of brackets. Go figure.
ATBCharles Kincaid
January 12, 2010 at 7:14 am
m.ciesiensky (1/6/2010)
Timothy,when attempting to run sp_msforeachtable in each of the variations below, I get these errors. I have tried running from both the master and CBL databases, with all same results.
Any Ideas?
exec dbo.sp_msforeachtable 'select * from CBL.dbo.[?]'Msg 208, Level 16, State 1, Line 1
Invalid object name 'CBL.dbo.[dbo].[ae_amap]'.
Any help from anyone will be much appreciated.
The ? in sp_msforeachtable includes the schema already and is already quoted. In other words, remove the dbo. and the brackets and it should work. Something like:
exec sp_msforeachtable 'select * from ?'
if you are doing it inside CBL or
exec sp_msforeachtable 'select * from CBL.?'
if you want or need to fully qualify it.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply