query to search a column

Hi,
I know the column name but I don't know in which table it is existing. It
will take a lot of time if I search table by table..
Is there a way/query to find in which table a particular column is existing?
please let me know if there is one..
Thanks
Chandraselect * from information_schema.columns
where column_name='MyColumn'
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:270985E4-CB80-43DB-866C-411F0C207B5C@.microsoft.com...
> Hi,
> I know the column name but I don't know in which table it is existing. It
> will take a lot of time if I search table by table..
> Is there a way/query to find in which table a particular column is
existing?
> please let me know if there is one..
> Thanks
> Chandra
>|||Chandra
You can you the INFORMATION_SCHEMA.COLUMNS view as demonstrated below,
replacing NameOfColumnToSearchFor with the name of the column you are
searching for. This view contains one row for each column accessible to the
current user in the current database.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'NameOfColumnToSearchFor'
- Peter Ward
WARDY IT Solutions
"Chandra" wrote:

> Hi,
> I know the column name but I don't know in which table it is existing. It
> will take a lot of time if I search table by table..
> Is there a way/query to find in which table a particular column is existin
g?
> please let me know if there is one..
> Thanks
> Chandra
>|||Chandra
this is one of the ways.
select table_name from INFORMATION_SCHEMA.COLUMNS where column_name
=<columnname>
But careful when you have more than one table having same column name
Regards
R.D
"Chandra" wrote:

> Hi,
> I know the column name but I don't know in which table it is existing. It
> will take a lot of time if I search table by table..
> Is there a way/query to find in which table a particular column is existin
g?
> please let me know if there is one..
> Thanks
> Chandra
>|||Thanks Moshe!!! It solves my problem.
"Moshe" wrote:

> select * from information_schema.columns
> where column_name='MyColumn'
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:270985E4-CB80-43DB-866C-411F0C207B5C@.microsoft.com...
> existing?
>
>