SQL: How to find a column in the whole database?

Question

I need to find a column in which table is it in, What is the right query that will find this column?

The search should be by the name of column in all tables.

Thanks in advance.

Answer 1

That depends on what database engine you are using. Most databases have tables that contain the database structure and often have database  specific commands to show them.

For instance, in MySQL you can use "show tables" to get a list of all the table names in the database, then recursively run through the tables using the "describe tables" command to get all the column names to compare to the one you are seeking.

Answer 2

I think you know the answer

if you are searching for particular column in the Database, you specify which column you want, then followed by where statement

e.g SELECT ColName1,ColID FROM TABLE

WHERE ColID=@colID

that parameter will allow the user to input colID

Answer 3

Standard way:

select table_name from information_schema.columns where column_name='YOUR_COLUMN_NAME'

Answer 4

Hi

This will work

select name from sysobjects where id in(select id from syscolumns where name ='columnName')

Answer 5

If you are using Microsoft SQL Server, I think the following command will work:

SELECT o.name FROM master..syscolumns c, master..sysobjects o WHERE c.name = "columnname" AND c.id = o.id;

o.name will be the name of the table(s) containing "columnname".

This will not work in other versions of SQL because it uses sysobjects and syscolumns which are likely Microsoft-specific.

Answer 6

Please be more specific in your question. But since you're asking at Programming and Design category, I'll assume you will be searching for the column using a Programming language( like C#.Net ).

heres a tip.

1. first you need to connect to your database(SQL Server for example)

string connectionStr = "server=localhost;database=Northwind;

uid=sa;pwd=sa";

SqlConnection _sqlConnection =

new SqlConnection( connectionStr );

2. Second, you need to get all the tables on that database and put the resultset to a DataTable

DataTable dtTemp = new DataTable("Tables");

string _sqlStr = "Select * from tab";

SqlDataAdapter adapter = new SqlDataAdapter( _sqlStr, _sqlConnection );

adapter.Fill( dtTemp );

3. From the list of tables, execute a query to get all the columns of that table.

foreach( DataRow dRow in dtTemp.Rows )

{

string _tableName = dRow[0].ToString();

string _sqlStrColumns = "Select * from " + _tableName;

SqlAdapter _tableAdapter = new SqlAdapter( _sqlStrColumns, _sqlConnection );

DataTable myTable = new DataTable(_tableName);

_tableAdapter.Fill(myTable);

foreach(DataColumn dtCol in myTable.Columns )

{

if(dtCol.ColumnName = "myColumnToSearch")

{

return _tableName;

}

}

}

hope this helps.