开发者

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].[Sal开发者_开发技巧esTerritoryHistory]
[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]


Have you tried adding brackets?

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


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'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜