CODEWITHSUNDEEP
sqlregexdatabase
user3441895
user3441895

Reputation: 1

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.

Thanks

Upvotes: 0

Views: 4834

Answers (1)

Doug_Ivison
Doug_Ivison

Reputation: 650

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

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'

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.

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.

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*').

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.

Hope that helps...

Upvotes: 1

Related Questions