Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Finding the tables that contain fieldX

Posted on 2000-05-04
5
Medium Priority
?
360 Views
Last Modified: 2008-02-20
I can check syscolumns to find how many tables contain a particular field in a database, but is there a way to pinpoint the tables those fields are in?
0
Comment
Question by:hemphandle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2776762
select o.name from sysobjects o join syscolumns c on o.id = c.id where c.Name = "id"

This returns the list of tables in the current database where column name is "id".
Is this what you are looking for?
0
 
LVL 5

Expert Comment

by:dtomyn
ID: 2777108
Perhaps better yet (if angelIII and I am following your question correctly) is to use INFORMATION_SCHEMA.COLUMNS
i.e.
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = "id"
0
 

Accepted Solution

by:
USP earned 200 total points
ID: 2778219
You can also write the stored procedure which can accept the column name as parameter & generate the list of all the table like...

*--------------*
Create Procedure Sp_FindColumn
@MyCol varchar(255)
As
Select o.name As ResourceTable
From sysobjects o
Join syscolumns c
On o.id = c.id
Where c.Name = @mycol
*----------------*
0
 
LVL 5

Expert Comment

by:dtomyn
ID: 2778273
Gee thanks USP, your "Answer" is SO much better than mine and ESPECIALLY angelIII's!

Thanks again for adding your extreme insight on this one.  You really deserve those points!
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2789206
hemphandle,
Check this one (Better and faster):

create proc spFindCol
@snColName sysname=null
as
begin
  set nocount on
  select object_name(id)
    from syscolumns
   where name=@snColName
end
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question