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.

score 4 · Answer 1 · answered May 07 '10 at 14:40

4

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'

answered May 07 '10 at 14:40

Robert Harvey

178,213
47
333
501

Good, query 1 gives me multiple tables since various tables have CityID as a FK. Is it possibility to shortlist the table that has it as the PK. – Salman A May 07 '10 at 15:21
1

For that, see http://stackoverflow.com/questions/222217/how-do-i-determine-if-a-column-is-in-the-primary-key-of-its-table-sql-server – Robert Harvey May 07 '10 at 15:24

score 3 · Answer 2 · answered May 07 '10 at 14:40

3

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

answered May 07 '10 at 14:40

Wudang

553
3
17

score 2 · Answer 3 · answered May 07 '10 at 14:42

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

score 1 · Accepted Answer · answered May 07 '10 at 14:43

1

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

answered May 07 '10 at 14:43

AakashM

62,551
17
151
186

Can I figure out if the column is a PK or FK? Currently it returns multiple tables as ~30 tables have this field as FK but only one has this field as PK. – Salman A May 07 '10 at 15:38
@Salman A : for that I think you want to look in `TABLE_CONSTRAINTS` and `CONSTRAINT_COLUMN_USAGE` (or maybe `KEY_COLUMN_USAGE`). These views tend to be fairly readable in terms of working out what goes where; have a look around the docs I linked. – AakashM May 07 '10 at 16:35

score 0 · Answer 5 · answered Oct 06 '18 at 19:10

As I understand from your question, you want to find tables which contain CITYID column in primary key

You can use SQL Server system views like sysindexes and sysindexkeys as shown in SQL tutorial to query database table primary keys including composite primary keys which are formed

SELECT
  TBL.name as TableName
FROM sysobjects as PK
INNER JOIN sys.objects as TBL
  on TBL.object_id = PK.parent_obj
INNER JOIN sysindexes as IND
  on IND.name = PK.name AND
  IND.id = TBL.object_id
INNER JOIN SysIndexKeys as KEYS
  on KEYS.id = IND.id AND
  KEYS.indid = IND.indid
INNER JOIN syscolumns as COL
  on COL.id = KEYS.id AND
  COL.colid = KEYS.colid
WHERE
  PK.xtype = 'PK' AND
  COL.name = 'CityID'

Dynamically determining table name given field name in SQL server

5 Answers5