Find Table name by column name? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find Table name by column name?

Author

Topic

cgl_milty
Starting Member

2 Posts

Posted - 2005-12-12 : 10:33:11

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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-12 : 10:55:10

Look up the "information_schema" views. Search here for examples of their usage.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-12 : 12:41:37

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.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-12 : 16:35:30

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%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 00:52:56

Select table_name from information_Schema.columns where column_name='yourCol'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 02:49:11

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

cgl_milty
Starting Member

2 Posts

Posted - 2005-12-13 : 11:33:33

WOW! Thank you so much everyone. I'm slowly working through these suggestions, and I'll let you know how it turns out!

supersql
Yak Posting Veteran

99 Posts

Posted - 2008-10-30 : 13:34:46

how about store procs where that column is used.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 13:47:52

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'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 02:30:00

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

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-10-31 : 16:39:05

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-31 : 19:18:25

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources