constraints without names (like PK__STRUCT_TYPE__0377368A)
-
If one creates a constraint on a table and doesn't provide a name for the constraint the system will create some name for that constraint. This name has a random number in the end, like for example: PK__STRUCT_TYPE__0377368A or FK__MESSAGE__0E61D4E3
Does anybody know how I can find all these kind of constraints without explicit names as I would like to give them names that we can refer in scripts and applications.
I tried something using the status field in sysobjects but it doesn't seem to work 100%.
Any help will be appreciated.
Thank you.
Gabriela
-
select object_name(parent_obj) as tablename, name, xtype, crdate
from sysobjects
where xtype in ('C ','PK','UQ','F ', 'D ')
order by tablename
-
I think I wasn't very clear in what I want: I do not want a list of all constraints in the database, but a list of those that were generated by the system because an explicit name wasn't provided when the constraint was created!
Gabriela
-
This should work
SELECT CAST(S1.[name] AS VARCHAR(30)) SystemGeneratedName, CAST(S2.[name] AS VARCHAR(50)) ObjectName
FROM sysobjects S1
LEFT JOIN sysobjects S2
ON S1.parent_obj = S2.[id]
WHERE
LEFT(S1.[name], 4) IN ('CK__', 'DF__', 'PK__', 'FK__')
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks, I was thinking about parsing the name, but who can stop me to explicitly call a PK with "PK__TABLE__AA23456FF"? Looks like one generated by the system, but is explicit. If you try to script the table as "CREATE" and the table has a PK that is named by the system you will see something like:
PRIMARY KEY NONCLUSTERED
(
[INFO_CODE]
 
ON [IXGroup]but if the PK was created with name, even a funny one, you will see something like:
CONSTRAINT [PK__TABLE__AA23456FF] PRIMARY KEY NONCLUSTERED
(
[INFO_CODE]
 
SQL "knows" which are its names! What I want to know is how? Must be a bit set somewhere!
Gabriela
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply