1

I have a database, with some tables in which, some fields have constraints. I have named some of these constraints myself, but some others, the names are generated automatically.

I want to select all these constrains. I am using 

SELECT * FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

to get all the constraints. However there are a lot, and I cannot find them manually. These names are unpredictable with alphanumeric characters like PK__getParen__50EEF97F6BC33CCA How do I separate constraints I have named myself and automatically named?

kkica
  • 4,034
  • 1
  • 20
  • 40
  • 2
    Possible duplicate of [SQL Server 2008- Get table constraints](https://stackoverflow.com/questions/14229277/sql-server-2008-get-table-constraints) – Sandip Bantawa Oct 12 '17 at 10:59
  • Seems somewhat similar, but this is focused on finding constraints with system-generated names. – mendosi Oct 12 '17 at 11:06

3 Answers3

2

The sys.check_constraints, sys.key_constraints, sys.default_constraints, sys.foreign_keys views contain a column is_system_named which should tell you if the name of the constraint was automatically generated.

mendosi
  • 2,001
  • 1
  • 12
  • 18
0

There are a number of CONSTRAINT related Views available in the 'System Views' Folder in the 'Views' Folder in the 'master' database folder in the 'System Databases' folder in the 'Databases' folder on Object Explorer...

That is: unless it is already open, open Object Explorer from the View Tab.

Databases > System Databases > master > Views > System Views.

Just run them as (eg) SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS and you should find what you are looking for.

kkica
  • 4,034
  • 1
  • 20
  • 40
russ
  • 579
  • 3
  • 7
0

Try something like :

 SELECT * 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME NOT LIKE 'PK_%'
Khorshed Alam
  • 314
  • 2
  • 11