sp_msforeachtable不给我正确的结果
问题描述:
我想使用sp_msforeachtable为数据库中的某些表执行一些工作。我使用IF语句来过滤表。但它并没有给我正确的答案。如以下脚本所示,我使用AdventureWorks进行测试。我想在每张桌子上做一些工作,除了Person.Address,Person.Contact,Person.CountryRegion。如你所见,这些表格仍然包含在结果中。为什么?谁能帮我解决我的问题?万分感谢。sp_msforeachtable不给我正确的结果
sp_msforeachtable '
IF ''?'' <> ''Person.Address'' AND ''?'' <> ''Person.Contact'' AND ''?'' <> ''Person.CountryRegion''
BEGIN
PRINT ''?''
END
';
结果是:
[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]
答
您是否尝试过加入括号?
sp_msforeachtable '
IF ''?'' <> ''[Person].[Address]'' AND ''?'' <> ''[Person].[Contact]'' AND ''?'' <> ''[Person].[CountryRegion]''
BEGIN
PRINT ''?''
END
';
答
为什么不直接使用系统表,因为它显示了要所有表不是在人模式?
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'
上帝,你的解决方案有效。但为什么?我们不能在单引号中使用模式名称和对象名称吗? – 2010-06-30 14:38:17
@Yousui:看看输出 - ms_foreachtable似乎总是使用括号 - 所以你不能比较没有括号的东西...这只是一个字符串格式化的问题..... – 2010-06-30 14:49:03
appart从无到有,我没有什么补充说:) – 2010-06-30 20:05:23