• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Find all tables with a Column name.

Hi,

How can I determine all table's that contain a particular column name.

Ie: I want to know all tables that contain the ProductCode field.

Regards
Plucka
0
Plucka
Asked:
Plucka
  • 2
1 Solution
 
amit_gCommented:
Try this...

select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'ProductCode'
0
 
PluckaAuthor Commented:
Thanks
0
 
andrewbleakleyCommented:
See if this gives you what you want
---------

SELECT     syscolumns.name AS col_name, sysobjects.name AS table_name
FROM         syscolumns LEFT OUTER JOIN
                      sysobjects ON syscolumns.id = sysobjects.id
WHERE     (syscolumns.name = N'EMPLOYEE_CODE') AND (sysobjects.type = 'U')
0
 
PluckaAuthor Commented:
I'm happy to post another question.

But I need to do this to rename a product in all related tables. But was wondering.

The above query also return's views. So I need to know the following

1. Does an update statement work on a view?
2. Can the above query be changed to only show tables.

Regards
Plucka
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now