Find Tables Where a Column is Used in Primary Key Definition
On SQL Server, to find the list of database tables where a specific column based on column name is used in primary key definitions, T-SQL developers can use the SQL Select script which I shared in this tutorial.
For example, if you are looking for all tables where ProductID column is used in the primary key definitions of a database table in SQL Server sample database AdventureWorks, you might be expecting to get a list of database tables:
Product, ProductDocument, ProductInventory, ProductVendor, WorkOrderRouting, etc.
All these tables have a primary key defined and in that primary key (it might be a composite key) the ProductId column name is used as a part of the key.
Let's first execute following SQL SELECT statement which returns the list of table names on a SQL Server database for a given column filtered in all table primary key index list.
SELECT
COL.name as ColumnName,
PK.name as PrimaryIndexName,
SCHEMA_NAME(TBL.schema_id) as SchemaName,
TBL.name as TableName
FROM sysobjects as PK -- Primary Key Table
INNER JOIN sys.objects as TBL -- Table of PK
on TBL.object_id = PK.parent_obj
INNER JOIN sysindexes as IND -- Index Definition
on IND.name = PK.name AND
IND.id = TBL.object_id
INNER JOIN SysIndexKeys as KEYS -- Index Keys
on KEYS.id = IND.id AND
KEYS.indid = IND.indid
INNER JOIN syscolumns as COL -- Columns
on COL.id = KEYS.id AND
COL.colid = KEYS.colid
WHERE
PK.xtype = 'PK' AND
COL.name = 'ProductID'
Database developers can see in the output of the above SQL script that all tables are listed with their primary index names for filtered table field name in WHERE clause
If you check the SQL code, database developers will realize that the main solution for this task comes from usage of system views like sysobjects, sysindexes, SysIndexKeys and syscolumns.
SQL programmers can also convert the above SELECT command into a user-defined table-valued SQL function, if it will be used frequently
CREATE FUNCTION fnListTablesByPrimaryKeyField (
@fieldName as SYSNAME
)
RETURNS @TableList Table (
ColumnName SYSNAME,
PrimaryKeyName SYSNAME,
SchemaName SYSNAME,
TableName SYSNAME
)
AS
BEGIN
Insert Into @TableList
SELECT
COL.name as ColumnName,
PK.name as PrimaryIndexName,
SCHEMA_NAME(TBL.schema_id) as SchemaName,
TBL.name as TableName
FROM sysobjects as PK -- Primary Key Table
INNER JOIN sys.objects as TBL -- Table of PK
on TBL.object_id = PK.parent_obj
INNER JOIN sysindexes as IND -- Index Definition
on IND.name = PK.name AND
IND.id = TBL.object_id
INNER JOIN SysIndexKeys as KEYS -- Index Keys
on KEYS.id = IND.id AND
KEYS.indid = IND.indid
INNER JOIN syscolumns as COL -- Columns
on COL.id = KEYS.id AND
COL.colid = KEYS.colid
WHERE
PK.xtype = 'PK' AND
COL.name = @fieldName
RETURN;
END
Then the table-value SQL function can be executed as seen in below sample code
select * from dbo.fnListTablesByPrimaryKeyField('ProductID')
The output of the above SQL function code will be same with first code sample