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)