CODEWITHSUNDEEP
t-sqlsql-server-2005sp-msforeachtable
Just a learner
Just a learner

Reputation: 28572

sp_msforeachtable does not give me the right result

I want to use sp_msforeachtable to do some work for some tables in a database. I use the IF statement to filter the tables. But it doesn't give me the right answer. As the following script shows, I use AdventureWorks to do the testing. I want to do some work on every table except Person.Address, Person.Contact, Person.CountryRegion. As you can see, these tables still included in the result. Why? Who can help me with my problem? Great thanks.

sp_msforeachtable '
IF ''?'' <> ''Person.Address'' AND ''?'' <> ''Person.Contact'' AND ''?'' <> ''Person.CountryRegion''
BEGIN
    PRINT ''?''
END
';

The result is:

[Sales].[Store]
[Production].[ProductPhoto]
[Production].[ProductProductPhoto]
[Sales].[StoreContact]
[Person].[Address] <------------------------------
[Production].[ProductReview]
[Production].[TransactionHistory]
[Person].[AddressType]
[Production].[ProductSubcategory]
[dbo].[AWBuildVersion]
[Production].[TransactionHistoryArchive]
[Purchasing].[ProductVendor]
[Production].[BillOfMaterials]
[Production].[UnitMeasure]
[Purchasing].[Vendor]
[Purchasing].[PurchaseOrderDetail]
[Person].[Contact] <------------------------------
[Purchasing].[VendorAddress]
[Purchasing].[VendorContact]
[Purchasing].[PurchaseOrderHeader]
[Sales].[ContactCreditCard]
[Production].[WorkOrder]
[Person].[ContactType]
[Sales].[CountryRegionCurrency]
[Production].[WorkOrderRouting]
[Person].[CountryRegion] <------------------------------
[Sales].[CreditCard]
[Production].[Culture]
[Sales].[Currency]
[Sales].[SalesOrderDetail]
[Sales].[CurrencyRate]
[Sales].[Customer]
[Sales].[SalesOrderHeader]
[Sales].[CustomerAddress]
[HumanResources].[Department]
[Production].[Document]
[HumanResources].[Employee]
[Sales].[SalesOrderHeaderSalesReason]
[Sales].[SalesPerson]
[HumanResources].[EmployeeAddress]
[HumanResources].[EmployeeDepartmentHistory]
[HumanResources].[EmployeePayHistory]
[Sales].[SalesPersonQuotaHistory]
[Production].[Illustration]
[Sales].[SalesReason]
[Sales].[Individual]
[Sales].[SalesTaxRate]
[HumanResources].[JobCandidate]
[Production].[Location]
[Sales].[SalesTerritory]
[Production].[Product]
[Sales].[SalesTerritoryHistory]
[Production].[ScrapReason]
[HumanResources].[Shift]
[Production].[ProductCategory]
[Purchasing].[ShipMethod]
[Production].[ProductCostHistory]
[Production].[ProductDescription]
[Sales].[ShoppingCartItem]
[Production].[ProductDocument]
[Production].[ProductInventory]
[Sales].[SpecialOffer]
[Production].[ProductListPriceHistory]
[Sales].[SpecialOfferProduct]
[Production].[ProductModel]
[Person].[StateProvince]
[Production].[ProductModelIllustration]
[dbo].[DatabaseLog]
[Production].[ProductModelProductDescriptionCulture]
[dbo].[ErrorLog]

Upvotes: 2

Views: 439

Answers (2)

Dion Brown
Dion Brown

Reputation: 136

Why not just use the system tables as it appears that you want all tables not in the Person schema?

    select sys.schemas.name + '.' + sys.tables.name from sys.tables 
    inner join sys.schemas on sys.tables.schema_id = sys.schemas.schema_id 
    where sys.schemas.name <> 'Person'

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

Have you tried adding brackets?

sp_msforeachtable '
IF ''?'' <> ''[Person].[Address]'' AND ''?'' <> ''[Person].[Contact]'' AND ''?'' <> ''[Person].[CountryRegion]''
BEGIN
    PRINT ''?''
END
';

Upvotes: 3

Related Questions