msforeachtable

Question

msforeachtable

rpatil22

Ten Centuries

Points: 1371
More actions
December 18, 2009 at 8:42 am

#214906

How to avoid some tables while using msforeachtable?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

the best way is to switch to your own cursor, and do whatever you were going to do to the specific tables;

here's a generic example; note how you could exclude just specific tables, or do it the better way and select only the tables you REALLY need:

declare

@isql varchar(2000),

@tbname varchar(64)

declare c1 cursor for

--EXAMPLE1

--every table except specific ones

--select name from sys.tables where name not in ('accounting','orders','invoices','payroll')

--EXAMPLE 2

-- only the suite of tables startingwith exp

--select name from sys.tables like 'EXP%'

--EXAMPLE3

--only tables with the column name i was looking for

select object_name(object_id) from sys.columns where name ='COMPLETEDDT'

open c1

fetch next from c1 into @tbname

While @@fetch_status <> -1

begin

select @isql = 'UPDATE @tbname SET COMPLETEDDT = GETDATE() where COMPLETEDDT IS NULL'

select @isql = replace(@isql,'@tbname',@tbname)

print @isql

exec(@isql)

fetch next from c1 into @tbname

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

You can actually provide a criteria to sp_MSForEachTable. Like this:

sys.sp_MSforeachtable

@command1 = 'Select * from ?', -- nvarchar(2000)

@whereand = 'and O.name not like ''%sys%'''

You need the alias as the sysobjects table is aliased as O. At least it is in 2000/2005/2008.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question