Solved

Get all unique constraints in database

Posted on 2013-06-06
3
263 Views
Last Modified: 2013-06-20
I'd like to get all unique constraints in database for all tables) and like to get result in below format:

SchemaName, TableName, UniqueConstraintName, ColumnName

How to achieve it? Please assist.
0
Comment
Question by:Easwaran Paramasivam
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 total points
ID: 39225556
Here's a start...

SELECT
      OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
      SCHEMA_NAME(schema_id) AS SchemaName,
      OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO
0
 
LVL 10

Assisted Solution

by:Asim Nazir
Asim Nazir earned 167 total points
ID: 39225567
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 166 total points
ID: 39225596
Same query given by jimhorn

SELECT 
      OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
      SCHEMA_NAME(schema_id) AS SchemaName,
      OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc in ('PRIMARY_KEY_CONSTRAINT','UNIQUE_CONSTRAINT')

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question