Dynamically determining table name given field name in SQL server

Question

Strange situation: I am trying to remove some hard coding from my code. There is a situation where I have a field, lets say "CityID", and using this information, I want to find out which table contains a primary key called CityID.

Logically, you'd say that it's probably a table called "City" but it's not... that table is called "Cities". There are some other inconsistencies in database naming hence I can never be sure if removing the string "ID" and finding out the plural will be sufficient.

Note: Once I figure out that CityID refers to a table called Cities, I will perform a join to replace CityID with city name on the fly. I will appreciate if someonw can also tell me how to find out the first varchar field in a table given its name.

Answer 1

SELECT name FROM sysobjects 
WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )

To get column information from the specified table:

SELECT column_name, data_type, character_maximum_length 
FROM information_schema.columns
WHERE table_name = 'myTable'

Answer 2

select table_name from information_schema.columns where column_name='CityID'

Answer 3

You can use the INFORMATION_SCHEMA tables to read metadata about the database.

SELECT 
     TABLE_NAME 
FROM 
     [db].[INFORMATION_SCHEMA].[COLUMNS]
WHERE 
     COLUMN_NAME='CityID';

For a primer in what's in the INFORMAITON_SCHEMA, see INFORMATION_SCHEMA, a map to your database

Answer 4

The information you seek is all available in the information schema views. Note that you will find many sources telling you how to directly query the underlying system tables that these are views onto - and I must admit that I do the same when it's just to find something out quickly - but the recommended way for applications is to go through these views.

For example, to find your CityID column:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CityID'

To find the first varchar field in a table:

SELECT TOP 1 * FROM INFORMATION_SCHEMA.COLUMNS WHERE
    TABLE_NAME = 'TableName'
    AND DATA_TYPE = 'varchar'    -- This is off the top of my head!
ORDER BY ORDINAL_POSITION