all tablenames having a specific column

Hi
Is there a way to get a list of all tables having a specific tablename
Ex. Give me all tables names having a column name 'customer_id'
Kind Regards
RoelHi,
select object_name(id) , name from syscolumns where name = 'customer_id'
Thanks
Hari
MCDBA
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

|||SELECT c.table_name
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE t.table_type = 'BASE TABLE'
AND c.column_name = 'customer_id'
Jacco Schalkwijk
SQL Server MVP
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:%23nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

|||Syscolumns not only includes the columns in each table, but also columns for
views and functions and parameters of stored procedures and functions.
What you want is:
select object_name(id) AS table_name , name from syscolumns where name =
'customer_id'
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
but using the information_schema views (see my other post) is simpler.
Jacco Schalkwijk
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPj021a6DHA.1664@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> select object_name(id) , name from syscolumns where name = 'customer_id'
> Thanks
> Hari
> MCDBA
> "Roel" <rvdbrand@.tycoint.com> wrote in message
> news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
>

---

|||SELECT SO.Name
FROM SysObjects SO INNER JOIN SysColumns SC
ON SO.ID = SC.ID
WHERE (
SO.XType = 'U'
AND SC.Name = 'YourColumnName'
)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||thx
all queyies have the same output
Regards
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---