T-SQL Find column name in database

Very often you need to use a specific field from sql server database table. The problem is how to find column name in database in any table using tsql? The answer is use information_schema and more precisely information_schema.columns

This is easy. Just open new query select the database from the drop down box you want to search and type

select *
from information_schema.COLUMNS
where column_name like '%name%'

The sql query above will show you every column that contains value ‘name’. If you have many results you can eliminate them by filtering it using the available columns. Usually table name field can be used to limit the results for instance if you are after name but for an employee you could try:

select *
from information_schema.COLUMNS
where column_name like '%name%' and table_name like '%emp%'

Did you find this page useful? +12  |  -0
(12 Votes)

Answered my question exactly
answer is accurate and works well
I got the desired result
string query gave me an idea.
answer was clear, concise and accurate. It was exactly what I needed
Want to add a comment

no comments

We had our legal consultation, and today we'll send our request to create a 'not for profit' company, to companies house.

£5,000 is our first target to cover basic operational costs, investments and salary for next 2-3 months.

just 7 days left
£867 ($1,140)
raised of £5,000 target

Sharing is caring :)
Pawel Olejniczak donated £20
Thomas Tabert donated $10
 Katrina Kilcoyne donated £20
 Jason Batters donated £20

Official Sponsors