Find out which table a field is in

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 2005 Forums

Transact-SQL (2005)

Find out which table a field is in

Author

Topic

VBScab
Starting Member

5 Posts

Posted - 2007-05-02 : 08:35:19

I have a generic routine into which fields from a web form are passed. For combo box-based fields, this will be a number. The fields are named such that they exactly match the column names in the database. For example, one of the fields is a 'Priority' field. The entries range from 1 to 5. On the web page, however, these are displayed in user-friendly forms - Low, Medium, High, Accelerated, Emergency and Exception. I am recording changes to this field (amongst others, hence the need to be generic) in another table. Although I record the number in the target table I want to display the user-friendly form to the user. Thus, the table entry will be 'Changed Priority from 1 to 5', I want the user to see 'Change Priority from Low to Exception'.

So, I need to look up the number and get the friendly text. However, being generic, I don't know which table the field is in and I want to avoid a (potentially) massive 'Select Case' construct which will in any case need editing every time we add/remove/rename fields.

*ALL* field names are unique, irrespective of which table they're in.

I think I need to query the system tables but I have no idea of the syntax. My psuedo-query:

SAY TABLE_NAME WHERE TABLE CONTAINS FIELD CALLED 'PRIORITY'

spejbl
Starting Member

28 Posts

Posted - 2007-05-02 : 08:41:10

[code]SELECT OBJECT_NAME(object_id)
FROM sys.columns
WHERE name='YourColumnName'[/code]

--
Tom
Microsoft KB articles monitoring | Apple KB articles monitoring

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 08:42:49

To know, which table contains given column:

SELECT C.TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T
ON C.TABLE_NAME = T.TABLE_NAME
WHERE COLUMN_NAME = 'PRIORITY' AND T.TABLE_TYPE = 'BASE TABLE'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

VBScab
Starting Member

5 Posts

Posted - 2007-05-02 : 08:47:22

Yup, that was it 'harsh_athalye'.

Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources