Get the list of all the constraints in a table in SQL Server

Often we think of How to get a list of all the constraints in a table. The below post answers that question. We can make use of INFORMATION_SCHEMA views to get the details of the meta data in SQL Server.

    • INFORMATION_SCHEMA view is contained in each database.
    • Each INFORMATION_SCHEMA view contains meta data about each object stored in that particular database.
      • information schema views are defined in a special schema named INFORMATION_SCHEMA.

The below query will list all the details about the meta data available in that particular database.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

For example the above query will give you the below output i.e. List of all the constraints in a particular database. You can even filter data by specific constraint type , schema name, table name etc.

 

image

Example 2: List all the constraint in a table Employee under Schema STTest.
Answer: Following query will result the correct answer

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME =‘Employee’ AND TABLE_SCHEMA =‘STTest’;

 

image

Note: Here TABLE_CONSTRAINTS is a view defined in a special schema named INFORMATION_SCHEMA.

Happy Learning !!!

Delete duplicate records from a SQL Server table

Today one of my colleague asked me how to delete duplicate records from a SQL Server Table. So to answer his question, let’s check the following example.

Note: The below example works on SQL Server 2005 and later versions.

–Create Table

CREATE TABLE dbo.TestDuplicate (EmpNo INT, EmpName VARCHAR(10));

–Insert Test Data

INSERT INTO dbo.TestDuplicate VALUES (1,‘Emp 1’);

INSERT INTO dbo.TestDuplicate VALUES (1,‘Emp 1’);

INSERT INTO dbo.TestDuplicate VALUES (2,‘Emp 2’);

INSERT INTO dbo.TestDuplicate VALUES (2,‘Emp 2’);

INSERT INTO dbo.TestDuplicate VALUES (3,‘Emp 3’);

–Remove Duplicates

WITH CTE
AS (SELECT ROW_NUMBER () OVER (ORDER BY EmpNo) SrNo , EmpNo FROM dbo.TestDuplicate)
DELETE FROM CTE WHERE SrNo NOT IN (SELECT MAX(SrNo) FROM CTE GROUP BY EmpNo);

–Check the table

SELECT * FROM dbo.TestDuplicate;

— Data before deleting the duplicate records.

image

— Data after deleting the duplicate records.

image

Points to Note:

  1. This was possible because of the ROW_NUMBER() function available in SQL 2005 onwards.
  2. ROW_NUMBER() function Returns the sequential number of a row within a partition of a result set. This means for each row in a result set it will return one unique sequential number for that row.
  3. The OVER clause determines the partitioning and ordering of the row set.
  4. As we have used CTE (Common Table Expression) it allows us to reuse the result set like a temporary table.

Hope this article may help you in deleting the duplicate records.