Filtering by column

Question

Post reply

Filtering by column

draybi01

SSC Rookie

Points: 46
More actions
December 20, 2010 at 3:04 pm

#232552

Hi.
I am using SQL Server 2005
I need to alter the certain query and to do this, I need to find other tables where certain column is used. I just joined the company and do not know the database structure yet, so I would like to see the tables where the column that I am interested in is used .. Basically I need to view all the tables where the column with certain name is present. I see filters of tables of certain name, etc.. however can't find how to filter by column name.
I know I used something like in Toad for Oracle ...
Can anyone help, please ...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

SELECT OBJECT_NAME(object_id)

FROMsys.columns

WHEREname = 'column name here'

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 2

I've enjoyed using Red Gate's free Search ad-in tool for questions like that. You can get it here:

http://www.red-gate.com/products/sql-development/sql-search/

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

draybi01 SSC Rookie Points: 46 More actions · Answer 3

draybi01

SSC Rookie

Points: 46

December 20, 2010 at 3:40 pm

#1264836

Thanks ...

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 4

Hi,

You can query from information schema view too, which provides the sql server metadata.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE column_name =''

This view provides both column name and tables names.

Thanks

John

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

This will allow you to view specific columns by name, or by data type along with their associated table name.

SELECT so.Name AS 'Table',sc.Name AS 'Column'

FROM sys.sysobjects so

LEFT OUTER JOIN sys.syscolumns sc

ON sc.id = so.id

WHERE so.type = 'u'AND sc.name = 'EmployeeId' --using column name

--or use AND sc.xtype = 40 for all columns with a data type of Date

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

bitbucket-25253 (12/20/2010)
This will allow you to view specific columns by name, or by data type along with their associated table name.
SELECT so.Name AS 'Table',sc.Name AS 'Column'
FROM sys.sysobjects so
LEFT OUTER JOIN sys.syscolumns sc
ON sc.id = so.id
WHERE so.type = 'u'AND sc.name = 'EmployeeId' --using column name
--or use AND sc.xtype = 40 for all columns with a data type of Date

Ron,

Eliminate the join... take a look at the OBJECT_NAME() function. It's a bit scope sensitive but so are both sys.objects and sys.sysobjects.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

bitbucket-25253 (12/20/2010)
This will allow you to view specific columns by name, or by data type along with their associated table name.
SELECT so.Name AS 'Table',sc.Name AS 'Column'
FROM sys.sysobjects so
LEFT OUTER JOIN sys.syscolumns sc
ON sc.id = so.id
WHERE so.type = 'u'AND sc.name = 'EmployeeId' --using column name
--or use AND sc.xtype = 40 for all columns with a data type of Date

Both syscolumns and sysobjects are deprecated, should not be used in new development and will be removed in a future version of SQL server. They're there only for backward compat with SQL 2000. The replacements are sys.objects and sys.columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass