SQL SERVER – How to Find UNIQUE Key Columns? – sp_special_columns

Unique key on a column is used to identify a particular row easily. It is created either by explicitly mentioning it either part of the definition or when defined as Primary key (By default it is unique and not null). Let us learn about special stored procedure sp_special_columns today.

Suppose you have lots of tables. You want to find out if the table has any unique key.

There can be several methods to do this. But one simple method I find is using the system stored procedure named sp_special_columns. This procedure returns the number of columns that can be uniquely identify a row

Let us create the following tables

USE TEMPDB
GO
CREATE TABLE customers(cust_id int unique, cust_name varchar(100))
GO
CREATE TABLE products1(product_id int primary key, prod_name varchar(100))
GO
CREATE TABLE products2(product_id int , prod_name varchar(100))
GO

Note that in the above, product2 does not have any unique columns

Now run these and see the results

 EXEC sp_special_columns customers
EXEC sp_special_columns products1
EXEC sp_special_columns products2

The first two statements return the result set showing the information about the columns that can uniquely identify a row (cust_id for table customers and product_id for table products1) . Whereas the third statement returns empty result set.

This way, you can easily identify if a particular table has any unique key.

Reference: Pinal Dave (https://blog.sqlauthority.com)