SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Find Table name by column name?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cgl_milty
Starting Member

Canada
2 Posts

Posted - 12/12/2005 :  10:33:11  Show Profile  Visit cgl_milty's Homepage  Reply with Quote
Hello everyone,

I have a database in which I must find a table name. I have a column name that exists in this table. I was hoping someone could help me with a command that would list all tables containing this column name. Is this feasible? Any and all help is appreciated.

Cheers!

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 12/12/2005 :  10:55:10  Show Profile  Reply with Quote
Look up the "information_schema" views. Search here for examples of their usage.
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 12/12/2005 :  12:41:37  Show Profile  Reply with Quote
Use of Information_Schema is more flexible.
But there is an easy way (though not as flexible as above):
Click F4 in Query Analyzer and search for your Column. U can use wild cards even.
Go to Top of Page

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 12/12/2005 :  16:35:30  Show Profile  Reply with Quote
select so.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name = 'YourColumnNameGoesHere'

or

select so.name, sc.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name like '%YourPartialColumnNameGoesHere%'
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 12/13/2005 :  00:52:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Select table_name from information_Schema.columns where column_name='yourCol'

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/13/2005 :  02:49:11  Show Profile  Reply with Quote
You might want to check th "owner" of the table too, and you might be on a case-sensitive databassde - in which case:

SELECT	TABLE_SCHEMA + '.' + TABLE_NAME
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	COLUMN_NAME = 'MyColumnName'

Kristen
Go to Top of Page

cgl_milty
Starting Member

Canada
2 Posts

Posted - 12/13/2005 :  11:33:33  Show Profile  Visit cgl_milty's Homepage  Reply with Quote
WOW! Thank you so much everyone. I'm slowly working through these suggestions, and I'll let you know how it turns out!
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 10/30/2008 :  13:34:46  Show Profile  Reply with Quote
how about store procs where that column is used.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/30/2008 :  13:47:52  Show Profile  Reply with Quote
quote:
Originally posted by supersql

how about store procs where that column is used.


whats the purpose of reopening old thread? you would have opened a new topic on this. Anyways, one solution to your problem is system sp sp_depends

just use

sp_depends 'yourcolumnname'
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 10/31/2008 :  02:30:00  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by supersql

how about store procs where that column is used.


whats the purpose of reopening old thread? you would have opened a new topic on this. Anyways, one solution to your problem is system sp sp_depends

just use

sp_depends 'yourcolumnname'



sp_depends wont work for columns

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 10/31/2008 :  16:39:05  Show Profile  Reply with Quote
I found this on one of the web-boards a while back. Works like a charm for searching stored procs for ANY string.

-- Create the numbers table
CREATE TABLE dbo.Numbers (Num INT NOT NULL PRIMARY KEY CLUSTERED);
GO

DECLARE @i INT;
SELECT @i = 1;
WHILE @i <= 10000
BEGIN
INSERT INTO dbo.Numbers(Num) VALUES (@i);
SELECT @i = @i + 1;
END;



SELECT DISTINCT O.Name, O.Type
FROM
(
SELECT Id,
CAST(COALESCE(MIN(CASE WHEN sc.colId = Num-1 THEN sc.text END), '') AS VARCHAR(8000)) +
CAST(COALESCE(MIN(CASE WHEN sc.colId = Num THEN sc.text END), '') AS VARCHAR(8000)) AS [text]
FROM syscomments SC
INNER JOIN numbers N
ON N.Num = SC.colid
OR N.num-1 = SC.colid
WHERE N.Num < 30
GROUP BY id, Num
) C
INNER JOIN sysobjects O
ON C.id = O.Id
WHERE C.text LIKE '%column_name%'


Edit - WooHoo!! 250 posts for me!! Watch out visakh, I'm closing in


Terry

Edited by - tosscrosby on 10/31/2008 16:41:35
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 10/31/2008 :  19:18:25  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Edit - WooHoo!! 250 posts for me!! Watch out visakh, I'm closing in



[1.01 posts per day]
Maybe only 30 years or so


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000