August 14, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/sysgenconstraintnames.asp
Gregory A. Larsen, MVP
August 15, 2002 at 1:45 am
I started off with SQL 6.5 when Enterprise Manager was given the euphenism Enterprise Mangler therefore we always used TRANSACT SQL and explicitly named every constraint.
SQL also creates system indices when it needs that cause the same problems as discussed in this article.
In SQL 6.5 if you didn't delete the indices BEFORE you DROPped a table then you would end up with an orphaned index, therefore I set up a routine to remove all constraints and indices prior to dropping the table.
In SQL 7/2000 my routine keeps hitting system indices and falling over.
Does anyone know a way of identifying system indices or constraints?
August 15, 2002 at 8:17 am
Try this:
-- finding system generated constraint names
select name from sysobjects
where
substring(name,rtrim(len(name))-8,len(name)) like '[__][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F]'
-- finding system genereated indexes
select name from sysindexes
where
name like '[_]WA%'
quote:
I started off with SQL 6.5 when Enterprise Manager was given the euphenism Enterprise Mangler therefore we always used TRANSACT SQL and explicitly named every constraint.SQL also creates system indices when it needs that cause the same problems as discussed in this article.
In SQL 6.5 if you didn't delete the indices BEFORE you DROPped a table then you would end up with an orphaned index, therefore I set up a routine to remove all constraints and indices prior to dropping the table.
In SQL 7/2000 my routine keeps hitting system indices and falling over.
Does anyone know a way of identifying system indices or constraints?
Gregory A. Larsen, MVP
August 15, 2002 at 9:26 am
I thought this at first, however it is not as simple as the naming convention.
Not all system indices start __WA etc.
The way I get around it at present is to use my own naming convention and anything that doesn't conform to my convention must be a system object.
This is fine when I have absolute control over my databases, however I need something more robust because my customers won't necessarily have the same methodology that I use.
August 15, 2002 at 9:41 am
What system generated indexes don't start with "_WA" (although these are not really indexes, only statistics)? If you are referring to system generated primary keys then try this on sysindexes:
select name from sysindexes
where
substring(name,rtrim(len(name))-8,len(name)) like '[__][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F][0-9,A-F]'
Of course this is not robost, unless all system generated names always end with a '_' followed by a 8 character hex number. Plus someone could also name there non-system generated name with this same convention.
If you are not talking about system generated primary keys then, what other indexes might you be referring too?
Gregory A. Larsen, MVP
August 15, 2002 at 10:00 am
I had a table called Tbl_CommenceDownload and noticed that I gained an index called tTbl_CommenceDownload.
I attempted to drop this and got the standard message that tells you that you are trying to drop a system object.
August 27, 2002 at 2:04 pm
Another reason to name your constraints is to help debug applications. A foreign key violation message states which column(s) in the key table were violated but doesn't tell you the referencing table or column names. My standard for naming foreign keys is as follows:
FK_<RefTableName>$<RefColumn1>$<RefColumnN>
Since the error message does show the contraint name, I get all the tables and columns involved in the error without having to step through every line of code in my script or application.
BTW, I use $ instead of underscores because some people (myself excluded) use underscores in their object names.
Bryant E. Byrd, MCDBA
SQL Server DBA/Systems Engineer
Intellithought, Inc.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
September 9, 2002 at 3:11 am
I DO name my constraints.
If SQL needs an index and there isn't one available it creates its own.
Sometimes an index is not appropriate but SQL may decide that it needs one.
In the particular example I have given my table is a flat intermediate file used for preprocessing data from an external source before appending it into my applicaton database.
May 9, 2008 at 11:02 am
I am 100% agree with this article. System generated name cause so much problem when you are comparing two databases. Lets say If you want to compare Development and production database with system generated constraints name , PK Name etc. It will throw bunch of differences. It is good practice to have manually defined name.
Thanks
Sandeep
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply