sql查询以搜索数据库中的列名 [英] sql query for searching column name in database

问题描述

我需要从特定数据库中获取所有列名吗?而且我想知道如何使用正则表达式来访问数据库中的特定列.

I need to get all the column names from a particular database? and i would like to know how to use regular expression to get to a particular column in database.

谢谢

推荐答案

告诉我们您使用的是哪种RDBMS很有帮助-因此我们可以为您提供 更多选项 －但是有一种方法可以用于任何带有ANSI SQL的RDBMS:使用 information_schema .(请参见 此Wikipedia链接 information_schema，作为ANSI标准.)

It would be helpful to tell us which RDBMS you're using -- so we can give you more options -- but there is a way to do this that is intended to work with any RDBMS with ANSI SQL: using information_schema. (See this Wikipedia link on information_schema, as an ANSI standard.)

要获取所有列，可以使用:

To get all columns, you can use:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS 

要获取特定于 列的列表，当您知道要查找的列的确切名称(例如 OrderNumber )时，可以使用:

To get a list for a specific column, when you know the exact name of the column you're looking for (for example OrderNumber), you could use:

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'OrderNumber'

但是，如果您不知道该列的确切名称和 确切大小写 ，以及您数据库的归类(或该列)可能区分大小写...那么您可以使用 LIKE 匹配一部分列名，甚至可以使用 UPPER 或 LOWER 函数.例如，如果您想查看所有可能是订单号的列，并且不确定是 OrderNo 或 OrderNumber 还是 ORDER_ID ，则可以使用以下代码:

But if you don't know the exact name and exact case of the column, and if the collation of your database (or the column) might be case-sensitive... then you can match part of a column name using LIKE, and even include all results regardless of case-sensitivity by using the UPPER or LOWER functions. If, for example, you wanted to see all columns that might be an order number, and you weren't sure if it was OrderNo or OrderNumber or ORDER_ID, you might use the following:

SELECT DISTINCT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE UPPER(COLUMN_NAME) LIKE '%ORDER%'
ORDER BY TABLE_NAME, COLUMN_NAME  --or by column_name, table_name, etc.

还请注意:数据库的排序规则也可能会影响 INFORMATION_SCHEMA 是否必须为大写...有些可能与 information_schema 一起使用(小写)，但有些不得.

Also note: the collation of your database may also affect whether INFORMATION_SCHEMA has to be upper case... some may work with information_schema (lower case), but some may not.

就使用 正则表达式 而言...可以追溯到了解RDBMS和正在开发的内容(例如您是否尝试进行匹配)在C#中使用RegEx吗?).在SQL中，使用 Like 会更容易(并且 这个问题.(2)作为MySQL的起点，请查看此问题的答案，看起来您可以使用< columnName>REGEXP< regExp> ，例如 WHERE COLUMN_NAME REGEXP"* Order *" .(3)对于PostGreSQL，请查看此问题，它看起来像〜* WHERE 子句中的* 将使您可以沿 WHERE("COLUMN_NAME"〜* E'* Order *')的行使用RegEx.

As far as using regular expressions goes... it goes back to knowing your RDBMS, and what you're developing (like are you trying to do the match in C# using RegEx?). Within SQL, using LIKE is going to be easier (and there's more here, about wildcards with LIKE, in SQL Server), but for the record: (1) T-SQL does not have support for regular expressions built in, but there are some resources listed in the answers to this question. (2) As a starting point in MySQL, look at the answers to this question, where it looks like you could use <columnName> REGEXP <regExp>, like WHERE COLUMN_NAME REGEXP "*Order*". (3) For PostGreSQL, check out this question, from which it looks like ~* in the WHERE clause will let you use RegEx, along the lines of WHERE ("COLUMN_NAME" ~* E'*Order*').

如果正则表达式非常重要，以至于您愿意创建使用它们的函数，则(对于SQL Server，无论如何)请查看这些链接 .

If Regular Expressions are so important, that you're willing to create functions to use them, then (for SQL Server, anyway) check out these links at Code Project, and at MSDN.

希望有帮助...

这篇关于sql查询以搜索数据库中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！