查询表和命名为关键字的列
假设 SQL Server 2005+。
A 部分：
对于任何用户，从系统/内部/元/任何表/视图（抱歉，不是数据库忍者）进行查询的规范方法是什么 使用 SQL Server 关键字（如 case）的表或列名称？
如果关键字列表不可查询，我不介意维护该列表，因为它仅随支持的 SQL Server 版本而变化（对吗？）。
查看 SQL Server 2005 中的可用视图，我可以轻松地从 INFORMATION_SCHEMA.COLUMNS 和 INFORMATION_SCHEMA.TABLES 查询此信息，但我想确保它来自尽可能最好的信息面向未来的位置。
B部分：
是否可以通过查询获取关键字列表？
更新：虽然是一个有用的概念，但我对转义有问题的列/表/等名称特别不感兴趣，因为我希望编写一个工具来检查对于与关键字共享名称的表/列/等，并向开发人员提供有用的警告。该工具将在我办公室的代码审查期间使用，以指出开发人员可能需要考虑重命名该实体。 （或者希望开发人员在代码审查之前进行代码审查是为了他们自己的利益！）我什至可以将其设置为在我的构建脚本中与持续集成一起使用，但这只是对未来的一个想法。
Assume SQL Server 2005+.
Part A:
What is the canonical way to query from the system/internal/meta/whatever tables/views (sorry, not a database ninja) for any user table or column names that use SQL Server keywords (like case)?
I don't mind maintaining the list of keywords if that's not query-able, as it only changes with versions of SQL Server supported (right?).
Looking at available views in SQL Server 2005, I can easily enough query this information from INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES, but I want to be sure it's from the best possible location for future-proofing.
Part B:
Is it possible to get the list of keywords via query?
UPDATE: While a useful concept, I'm specifically not interested in escaping the column/table/etc names in question because I'm hoping to write a tool that will check for tables/columns/etc that share names with keywords and provide useful warnings to developers. The tool would be used during code reviews at my office to point out that the developer might want to consider renaming the entity. (Or hopefully by the developer before code reviews for their own good!) I may even set it up for use with continuous integration in my build scripts, but that's only a thought for the future.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您应该正确引用所使用的名称。如果生成代码，请使用内置的 QUOTENAME 函数。不要构建已知关键字的列表，而是引用每个对象使用的每个名称，包括数据库名称、模式名称和对象名称。还要确保您始终遵守所涉及对象的正确大小写。作为最佳实践，请在区分大小写的排序规则服务器实例上进行开发。当部署在区分大小写的排序规则服务器上时，在不区分大小写的服务器排序规则（默认）上开发代码可能会导致生产中出现令人尴尬的失败。
对于 A 部分 就
个人而言，我实际上会选择 sys.columns 和 sys.objects。 INFORMATION_SCHEMA 视图也很好，而且理论上它们是“可移植的”，不过我更习惯于 SQL 特定的视图。我选择 sys.objects 与 sys.tables 因为它涵盖更多（例如视图）。我建议您还涵盖表值函数、表值参数类型（仅限 2008 年）以及在存储过程中声明的临时 #tables 和表 @variables。这将只留下客户端发送的批量中声明的临时#tables和表@variables，但这些基本上仅在客户端代码中。
You should properly quote the names used. If you generate code, use the built-in QUOTENAME function. Don't build a list of known keywords, instead quote every name used for every object, including database name, schema name and object name. Also make sure you always adhere to the correct case of the objects involved. As a best practice, develop on a case sensitive collation server instance. Developing code on case insensitive server collation (default) can lead to embarasing failures on production when deployed on case sensitive collation servers.
For Part A
Personally I would go for sys.columns and sys.objects actually. INFORMATION_SCHEMA views are also good, and they're 'portable' in theory, I'm just so much more used to the SQL specific ones though. I choose sys.objects vs. sys.tables because it covers more (eg. views). I would suggest you also cover table valued functions, table valued parameter types (in 2008 only) and temporary #tables and table @variables declared inside stored procedures. That would leave out only temp #tables and table @variables declared in batches sent by clients, but those are basically in client code only.
答：只需在您的标识符周围使用括号即可。
select [procedure].[case] from [procedure]B：我不确定你是否可以查询它们，但是有一个MSDN 页面 关于它。
如果您以编程方式需要这些，我建议您将它们全部插入到表中供您自己使用。
A: Just use brackets around your identifier.
select [procedure].[case] from [procedure]B: I'm not sure if you can query for them, but there is a MSDN page about it.
If you need these programmatically, I suggest you insert them all into a table for your own uses.
为什么需要知道关键字列表？ a：它们不会经常更改，b：对于任何常规代码（我不包括“sql server management studio”之类的代码），您可以只使用方括号：
Why do you need to know the list of keywords? a: they don't change very often, and b: for any regular code (I'm excluding things like "sql server management studio") you can just use square brackets: