To find unique constraint in a table

Question

To find unique constraint in a table

ikonrao

SSC Veteran

Points: 233
More actions
June 6, 2013 at 5:12 am

#278296

We have a table in which we want to know whether any of the columns has unique constraint defined.
Or if we can get create table statement for that table, not from sql server studio, but from another application. So it has to be through queries.
Regards...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ikonrao (6/6/2013)
We have a table in which we want to know whether any of the columns has unique constraint defined.
Or if we can get create table statement for that table, not from sql server studio, but from another application. So it has to be through queries.
Regards...

you can query sys.objects for any unique constraints (and PK's too, which are also unique)

the parent_object_id is the link to the table.

SELECT

OBJECT_NAME(parent_object_id) AS ObjectName,

*

FROM sys.objects

WHERE type IN( 'UQ' ) --('UQ','PK') to include PK's which are also unique

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

ikonrao (6/6/2013)
We have a table in which we want to know whether any of the columns has unique constraint defined.
Or if we can get create table statement for that table, not from sql server studio, but from another application. So it has to be through queries.
Regards...

sp_help [schema.table]

Lowell SSC Guru Points: 323518 More actions · Answer 3

SQLSACT (6/6/2013)
ikonrao (6/6/2013)
We have a table in which we want to know whether any of the columns has unique constraint defined.
Or if we can get create table statement for that table, not from sql server studio, but from another application. So it has to be through queries.
Regards...
sp_help [schema.table]

since he wants to process this from an application and not SSMS, that would require the application to process Multiple Active Result Sets (MARS) , as the constraints are like the third or fourth datatable from the results of that procedure.

much better to simply request the specific data from the metdata data tables instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

OTF SSCertifiable Points: 6104 More actions · Answer 4

ikonrao (6/6/2013)
We have a table in which we want to know whether any of the columns has unique constraint defined.
Or if we can get create table statement for that table, not from sql server studio, but from another application. So it has to be through queries.
Regards...

You can also use Sql Management Objects(SMO) to do this or even Powershell.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

This will return both unique constraints and primary keys:

select

[SCHEMA_NAME]= sc.name,

[TABLE_NAME]= t.name,

[CONSTRAINT_NAME]= k.name,

[CONSTRAINT_TYPE]= k.type_desc

from

sys.schemas sc

inner join

sys.tables t

onsc.schema_id = t.schema_id

inner join

sys.key_constraints k

ont.object_id = k.parent_object_id

order by

sc.name,

t.name,

k.name

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

I would go with querying the system tables to get the rows you need. Besides, who knows if sp_help is going to change from version to version? Granted, you could say the same thing about the system tables, but Microsoft has been pretty good about maintaining backwards compatibility in recent versions.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions