SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

table list with primary key including specific column name

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums







Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems