SQL Server 上的 information_schema 架构信息不可靠？
SQL Server 文档此处指出table_schema information_schema.tables 的字段“不可靠”，获取对象模式的正确方法是查询 sys.objects 。
谁能详细说明 information_schema.tables 报告的模式如何以及何时可能不正确？
The SQL Server documentation here says that the table_schema field of information_schema.tables is "unreliable" and that the correct way to get the schema of an object is to query sys.objects.
Can anyone elaborate on how and when the schema reported by information_schema.tables can be incorrect?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
遗憾的是，这个问题没有得到答复，只是出于代表的贪婪而发表了评论，更重要的是，为了将其从未答复的队列中删除，我将在答案中提出几点。
文档中的措辞不准确，正在更正中（请参阅连接#686118）。我不确定他们是否会一次性纠正 2005、2008 和 2008 R2 文档，或者旧版本是否会得到更新。关键是，我无法想象任一视图中的架构不正确的情况，但更重要的是，info_schema 不正确，而 sys.objects 是正确的。后者是不可能的 - info_schema 视图完全基于 sys.objects 视图（只需查看 SELECT OBJECT_DEFINITION (OBJECT_ID ('INFORMATION_SCHEMA.TABLES'));），因此，如果其中一个错误，那么它们都错误。可能存在一些模糊的情况，它们都可能不正确，但在当前版本中不会（例如，在 SQL Server 2000 中，启用配置选项
允许更新，从 sysusers 中删除拥有对象的用户- 今天并不真正相关或不可能，也不是我愿意尝试的事情，但这是我能想象的唯一会在任何时间点激发当前措辞的方式）。一般来说，应避免使用
INFORMATION_SCHEMA视图，而应使用 SQL Server 2005 中引入的目录视图（并从那时起进行了扩充）。为什么？因为随着新功能添加到 SQL Server，目录视图仍在不断开发，而 info_schema 视图却没有。正如我在评论中提到的，尝试在 info_schema 中查找有关过滤索引的信息。包含的列、XML 索引、身份/计算列、针对唯一索引的外键也是如此 - 这些都要么完全缺失，要么在 info_schema 视图中以不同的方式表示。在 Denali 中，他们为序列添加了一个 info_schema 视图，但这再次满足了标准的最低要求，并且不包含有关 SQL Server 特定实现细节的任何信息（例如，它是否已用尽，以及他们是否在将来您可以确定 info_schema 视图不会保留在循环中）。您坚持使用 info_schema 视图的唯一情况是 (a) 您正在编写需要跨 info_schema 兼容平台工作的元数据例程并且 (b) 您没有使用任何特定于平台的功能那将会被错过。除了多平台供应商工具之外，这可能是一种非常罕见的情况（即使在这种情况下，也可能会导致正在使用这些功能而工具没有选择这些功能的客户感到不满）。 p>INFORMATION_SCHEMA视图主题上张贴有关此不完整性的警告。我认为人们并不知道它们不是从 SQL Server 获取元数据的首选方式，谁能责怪人们没有看到这一点 - 毕竟，我们总是被告知使用符合标准的方法是“最佳实践”，而使用专有方法则相反。与许多数据库事物一样，“这取决于情况” - 但我怀疑，通常情况下，您最好使用sys目录视图，除非您处于那种罕见的情况下：仅使用 SQL Server 中标准通用的功能。我认为我还没有遇到过任何这种情况的实例，但如果它们确实存在，我会非常高兴地了解它们。我还在博客中讨论了
INFORMATION_SCHEMA的不可靠性：It's a shame this went unanswered and just commented so partially out of rep greed and more importantly to get it out of the unanswered queue, I'll throw a few points into an answer.
The wording in the documentation is not accurate, and it is in the process of being corrected (see Connect #686118). I'm not sure if they will correct the 2005, 2008 and 2008 R2 docs all at once, or whether the older versions will even get updated. The point is that I can not envision a case where the schema in either view is incorrect, but even more so, that info_schema is incorrect while
sys.objectsis correct. The latter is impossible - the info_schema view is based entirely on thesys.objectsview (just look atSELECT OBJECT_DEFINITION (OBJECT_ID ('INFORMATION_SCHEMA.TABLES'));), so if one is incorrect, they're both incorrect. There are probably obscure cases where they can both be incorrect, but not in current versions (for example, in SQL Server 2000, with the config optionallow updatesenabled, delete from sysusers a user who owns an object - not really relevant or possible today, and not something I'm willing to try, but it's the only one I can imagine would have motivated the current wording at any point in time).In general,
INFORMATION_SCHEMAviews should be avoided in favor of the catalog views introduced in SQL Server 2005 (and augmented since then). Why? Because the catalog views continue being developed as new features are added to SQL Server, while the info_schema views have not. As I mentioned in my comment, try to find information about filtered indexes in info_schema. Same goes for included columns, XML indexes, identity/computed columns, foreign keys against unique indexes - these are all either missing entirely or represented differently in the info_schema views. In Denali they added an info_schema view for Sequences but again this meets the bare minimum of the standard and doesn't include any information about SQL Server-specific implementation details (for example, whether it's exhausted, and if they add any new features in the future you can be sure the info_schema view will not be kept in the loop). The only case where you would stick to info_schema views is if (a) you are writing metadata routines that need to work across info_schema-compliant platforms AND (b) you aren't using any platform-specific features that will be missed. Aside from multi-platform vendor tools this is probably a pretty rare scenario (and even in that case may lead to displeased customers who are using those features and the tool didn't pick them up).INFORMATION_SCHEMAview topics in Books Online. I don't think it's very well known that they are not the preferred way to get metadata out of SQL Server, and who could blame folks for not seeing this - after all, we're always told that using standards-compliant methods is a "best practice" and using proprietary methods are the opposite. As with a lot of database things, "it depends" - but I suspect, more often than not, you're better off using thesyscatalog views unless you're in that rare scenario where you're using only the features in SQL Server that are common to the standard. I don't think I've come across a single instance in any capacity where this was the case, but I'm more than happy to learn of them if they do exist.I've also blogged about the unreliability of
INFORMATION_SCHEMAhere: