Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

msforeachtable Expand / Collapse
Author
Message
Posted Friday, December 18, 2009 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 29, 2011 7:49 AM
Points: 107, Visits: 626
How to avoid some tables while using msforeachtable?
Post #836383
Posted Friday, December 18, 2009 9:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #836437
Posted Friday, December 18, 2009 4:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #836704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse