Lorentz Vedeler Lorentz Vedeler - 6 months ago 10
SQL Question

Is it possible to find all primary keys that have system generated names in a database?

I know of a couple of different ways to find all primary keys in the db, but is it possible to filter the results, so that it only show primary keys that have system generated names? None of the attributes returned by these queries seem relevant, so I am guessing I'll have to join another table or call a function, but I can't find anything relevant.

SELECT *
FROM sys.all_objects
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT'

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'


The reason I want to show these results is so that I can find and rename these constraints in a large database.

Edit:
By system generated names I mean primary keys that have been created by just adding
PRIMARY KEY
behind the column name in the table definition, so that it gets a name like: PK__Countrie__5D9B0D2D28F35AE2

Answer

An auto-generated PK seems to contain 16 hexadecimal digits in its name.
So I would use this query and then still manually check the results from it. Why check them manually? Because maybe the above statement may be just something undocumented, and may not apply in future versions of SQL Server.

SELECT * 
FROM sys.all_objects 
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT'
and
name like '%[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]%'
Comments