Solved

Finding the tables that contain fieldX

Posted on 2000-05-04
5
322 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
5 Comments
 
LVL 142

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 100 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

862 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now