Identifying System Generated Constraint Names

Question

Post reply

Identifying System Generated Constraint Names

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
March 30, 2010 at 10:23 am

#213221

Hi
Im trying to write a query to identify all system named FK's, DFs etc... Using some slightly amended code that looks at FK Im trying to find all FK names that have a '__' ... but my like statement wont play ...So
a. how should I be writing the like statement
b. is there a better way of identifying all constraints that are system generated rather than doing seperate queries for FK's then defaults etc etc
many thanks for any words of wisdom
~Simon
select fkt.name as [FK table],rct.name as [Parent Table],fko.name, pc.name as [FK column]
from sys.foreign_key_columns fk
join sys.columns pc
on fk.parent_object_id = pc.object_id
and fk.parent_column_id = pc.column_id
join sys.objects fkt
on pc.object_id = fkt.object_id
join sys.schemas as fks
on fks.schema_id = fkt.schema_id
join sys.columns rc
on fk.referenced_object_id = rc.object_id
and fk.referenced_column_id = rc.column_id
join sys.objects rct
on rc.object_id = rct.object_id
join sys.schemas as rcs
on rcs.schema_id = rct.schema_id
join sys.objects fko
on fk.constraint_object_id = fko.object_id
where fko.name like '%[__]%'

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

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

nd_junk Valued Member Points: 69 More actions · Answer 1

Are you looking for the string '[__]' or the string '__'?

In your code you have the square brackets.

nd

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Underscores are a special case and represent any single char within an expression.

e.g:

where column_name like '_ones'

Would return Bones, Jones etc. etc.

You need to specify that you're specifically looking for underscores by wrapping them in square brackets:

where fko.name like '%[_][_]%'

Lowell SSC Guru Points: 323518 More actions · Answer 3

because the underscore has a special meaning in a LIKE statement, i think you need to do something like this instead to find two underscores back to back:

where fko.name like '%!_!_%' ESCAPE '!'

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!

Paul White SSC Guru Points: 150467 More actions · Answer 4

The newer system views have a column called is_system_named. This will be 1 for the ones you are interested in.

Examples:

sys.check_constraints

sys.foreign_keys

sys.default_constraints

The code will be simpler, and much more reliable than just looking for doubled underscores.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 5

thank you all for your help

Paul thats great I'll go for that appraoch ... far simpler !

~simon 🙂