CODEWITHSUNDEEP
sql-server-2005
Chris
Chris

Reputation: 2303

list of table which has the column in sql server 2005

In my database, I have many tables which has the column StudentId, how can I get the list of the table which has this column in sql server 2005?

Upvotes: 0

Views: 393

Answers (4)

Shadab Shah
Shadab Shah

Reputation: 1

SELECT t.name 
FROM sys.table t INNER JOIN sys.column c
    ON c.objectid = t.objectid AND c.name LIKE 'studentid'

Upvotes: 0

marc_s
marc_s

Reputation: 755148

I would also like to add that Red-Gate Software has an absolutely free tool called SQL Search that is just absolutely wonderful to use and does this job with ease.

enter image description here

Upvotes: 3

Ryk
Ryk

Reputation: 3102

Do a SELECT * FROM sys.columns WHERE name = 'colname' and then you can join that back to sys.tables on object_id. I dont have Sql Server on my pc now, but this should be very close.

Upvotes: 0

ashish.chotalia
ashish.chotalia

Reputation: 3746

SELECT * FROM 
INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.COLUMN_NAME='StudentId'

Upvotes: 1

Related Questions