How to find Columns

30 Aug 2006 3:14 PM

Shyam

Hello,

My question could be strange or silly sometimes, but wanted to check if
there are any possibilities.
Q: Is it possible to find out the existance of the Column in a huge DB which
has 100's of tables in it, say Col A exists in Table Z,but, and it's diffcult
to remember the columns in all the tables and you have to traverse each tble
to find out,which is time consuming. So, is it possible to find it using a
query.
Any thoughts/views are welcome

Thanks,
Shyham

30 Aug 2006 3:18 PM

Roy Harvey

Yes, it is quite easy. Just query INFORMATION_SCHEMA.COLUMNS.

Roy Harvey
Beacon Falls, CT

On Wed, 30 Aug 2006 08:14:03 -0700, Shyam
<Sh***@discussions.microsoft.com> wrote:

Show quoteHide quote

>Hello,
>
>My question could be strange or silly sometimes, but wanted to check if
>there are any possibilities.
>Q: Is it possible to find out the existance of the Column in a huge DB which
>has 100's of tables in it, say Col A exists in Table Z,but, and it's diffcult
>to remember the columns in all the tables and you have to traverse each tble
>to find out,which is time consuming. So, is it possible to find it using a
>query.
>Any thoughts/views are welcome
>
>Thanks,
>Shyham

30 Aug 2006 5:30 PM

Hari Prasad

Hi,

There is lots of methods to get that. One amoung is:-

For all versions:-

sp_columns null,null,null,'Column_name'

For SQL 2005:-

select Object_name(Object_id) as Table_name,name as Column_name from
sys.COLUMNS
Where Name='Column_name'

Thanks
hari
SQL Server MVP

Show quoteHide quote

"Roy Harvey" wrote:

> Yes, it is quite easy. Just query INFORMATION_SCHEMA.COLUMNS.
>
> Roy Harvey
> Beacon Falls, CT
>
>
> On Wed, 30 Aug 2006 08:14:03 -0700, Shyam
> <Sh***@discussions.microsoft.com> wrote:
>
> >Hello,
> >
> >My question could be strange or silly sometimes, but wanted to check if
> >there are any possibilities.
> >Q: Is it possible to find out the existance of the Column in a huge DB which
> >has 100's of tables in it, say Col A exists in Table Z,but, and it's diffcult
> >to remember the columns in all the tables and you have to traverse each tble
> >to find out,which is time consuming. So, is it possible to find it using a
> >query.
> >Any thoughts/views are welcome
> >
> >Thanks,
> >Shyham
>

30 Aug 2006 3:27 PM

SQL Menace

SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME =t.TABLE_NAME
WHERE t.TABLE_TYPE ='BASE TABLE'
AND COLUMN_NAME = 'Col A'

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Shyam wrote:
Show quoteHide quote

> Hello,
>
> My question could be strange or silly sometimes, but wanted to check if
> there are any possibilities.
> Q: Is it possible to find out the existance of the Column in a huge DB which
> has 100's of tables in it, say Col A exists in Table Z,but, and it's diffcult
> to remember the columns in all the tables and you have to traverse each tble
> to find out,which is time consuming. So, is it possible to find it using a
> query.
> Any thoughts/views are welcome
>
> Thanks,
> Shyham

30 Aug 2006 3:28 PM

ML

Next time check old posts first. ;)

This answer is just minutes old: http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=6b388cfa-961c-4392-967a-2fb6fd1b2618

ML

---
http://milambda.blogspot.com/