Hey Guys,

In this article we will discuss how we can get primary column name of any table programmatically. However we can see primary column name by just right click on table and see design view. But when we have dynamically create table and set primary key then might be we does not know the primary column name. So in this article we discuss about that.

Find Primary Column Name In SQL Server


In this process we use INFORMATION_SCHEMA. So first question we all have that.

What is INFORMATION_SCHEMA ?

The best way how to explain what the INFORMATION_SCHEMA database is would be – “This is the database about databases. It’s used to store details of other databases on the server”.

The INFORMATION_SCHEMA database is an ANSI standard set of views we can find in SQL Server, but also MySQL. Other database systems also have either exactly such or similar database implemented. It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…) stored on the server.

You could easily use this data to:

  • Check what’s on the server and/or in the database
  • Check if everything is as expected (e.g. compared to the last time you’ve performed this check)
  • Automate processes and build some complex code

We can do more things using INFORMATION_SCHEMA but in this particular we only use it for get primary column name. For this we use two view from INFORMATION_SCHEMA and join these two view for get data. The first view is INFORMATION_SCHEMA.TABLE_CONSTRAINTS and second view is INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

The view TABLE_CONSTRAINTS contains all constraints belonging to tables that the current user owns or has some privilege other than SELECT on.

There are following columns in this view.

  • CONSTRAINT_CATALOG : Name of the database that contains the constraint (always the current database)
  • CONSTRAINT_SCHEMA : Name of the schema that contains the constraint
  • CONSTRAINT_NAME : Name of the constraint
  • TABLE_CATALOG : Name of the database that contains the table (always the current database)
  • TABLE_SCHEMA : Name of the schema that contains the table
  • TABLE_NAME : Name of the table
  • CONSTRAINT_TYPE : Type of the constraint: CHECK, FOREIGN KEY, PRIMARY KEY, or UNIQUE
  • IS_DEFERRABLE : YES if the constraint is deferrable, NO if not
  • INITIALLY_DEFERRED : YES if the constraint is deferrable and initially deferred, NO if not

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

The view CONSTRAINT_COLUMN_USAGE identifies all columns in the current database that are used by some constraint. Only those columns are shown that are contained in a table owned by a currently enabled role. For a check constraint, this view identifies the columns that are used in the check expression. For a foreign key constraint, this view identifies the columns that the foreign key references. For a unique or primary key constraint, this view identifies the constrained columns.

There are following columns in this view.

  • TABLE_CATALOG : Name of the database that contains the table that contains the column that is used by some constraint (always the current database)
  • TABLE_SCHEMA : Name of the schema that contains the table that contains the column that is used by some constraint
  • TABLE_NAME : Name of the table that contains the column that is used by some constraint
  • COLUMN_NAME : Name of the column that is used by some constraint
  • CONSTRAINT_CATALOG : Name of the database that contains the constraint (always the current database)
  • CONSTRAINT_SCHEMA : Name of the schema that contains the constraint
  • CONSTRAINT_NAME : Name of the constraint

In my data base I have only one table Name Employee and you can see in below image the primary key column is EmployeeId.

 

Find Primary Column Name In SQL Server

Here we use two view as mention above so first we get what inside that view.Run following queries to get output of both views.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS 



Output

 

Find Primary Column Name In SQL Server

 

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE



 Output

 

Find Primary Column Name In SQL Server

Get Primary Key Column Name Programmatically: Here is few line of sql query using that we can get primary column name.



Output

Find Primary Column Name In SQL Server


Explanation:

  • Here we join this two view TABLE_CONSTRAINTS And CONSTRAINT_COLUMN_USAGE on CONSTRAINT_NAME.
  • Then select those record where CONSTRAINT_COLUMN_USAGE.TABLE_NAME is Employee and TABLE_CONSTRAINTS.CONSTRAINT_TYPE is Primary Key.
  • And then select CONSTRAINT_COLUMN_USAGE. COLUMN_NAME.

So that is just two or three lines of sql query for get primary column name of any table. I hope you find some information from this article. If you find this help full kindly share with your friends.