Discussion on: Name your Constraints

View post

I had this issue, when I first started developing my first project database I did not think to name my constraints and as such a year into the project I got access to red gates SQL Compare. Oh boy it took a couple of days to sort the small tedious differences that were not only randomly generated constraints but bad data base building practices in general that I was blind to in the beginning. Anyways I did find a nice script which will rename all constraints to use the correct convention or the one you choose.

I cannot remember where I found it but I think it was SQL Server Central.

ALTER PROCEDURE [dbo].[ImplementNamingStandard]
    @SELECT_Only        BIT = 1,
    @PrimaryKeys        BIT = 1,
    @ForeignKeys        BIT = 1,
    @Indexes            BIT = 1,
    @UniqueConstraints  BIT = 1,
    @DefaultConstraints BIT = 1,
    @CheckConstraints   BIT = 1
AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @sql NVARCHAR(MAX), @cr CHAR(2);
    SELECT @sql = N'', @cr = CHAR(13) + CHAR(10);


    DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT;
    SELECT @TableLimit = 35, @ColumnLimit = 35;


    IF @PrimaryKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Primary Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(name, '''', '''''') + ''', @newname = N''PK_' 
            + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.key_constraints
        WHERE type = 'PK'
        AND is_ms_shipped = 0;
    END


    IF @ForeignKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(name, '''', '''''') + ''', @newname = N''FK_' 
            + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit)
            + '_' + LEFT(REPLACE(OBJECT_NAME(referenced_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.foreign_keys
        WHERE is_ms_shipped = 0;
    END


    IF (@UniqueConstraints = 1 OR @Indexes = 1)
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Indexes / Unique Constraints ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
   + CASE is_unique_constraint WHEN 0 THEN
   QUOTENAME(REPLACE(OBJECT_NAME(i.[object_id]), '''', '''''')) + '.' ELSE '' END
            + QUOTENAME(REPLACE(i.name, '''', '''''')) + ''', @newname = N'''
            + CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE 'IX_'
              + CASE is_unique WHEN 1 THEN 'U_'  ELSE '' END 
            END + CASE has_filter WHEN 1 THEN 'F_'  ELSE '' END
            + LEFT(REPLACE(OBJECT_NAME(i.[object_id]), '''', ''), @TableLimit) 
            + '_' + STUFF((SELECT '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit)
                FROM sys.columns AS c 
                    INNER JOIN sys.index_columns AS ic
                    ON ic.column_id = c.column_id
                    AND ic.[object_id] = c.[object_id]
                WHERE ic.[object_id] = i.[object_id] 
                AND ic.index_id = i.index_id
                AND is_included_column = 0
                ORDER BY ic.index_column_id FOR XML PATH(''), 
                TYPE).value('.', 'nvarchar(max)'), 1, 1, '') +''';'
        FROM sys.indexes AS i
        WHERE index_id > 0 AND is_primary_key = 0 AND type IN (1,2)
        AND OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0;
    END


    IF @DefaultConstraints = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- DefaultConstraints ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(dc.name, '''', '''''') + ''', @newname = N''DF_' 
            + LEFT(REPLACE(OBJECT_NAME(dc.parent_object_id), '''',''), @TableLimit)
            + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';'
        FROM sys.default_constraints AS dc
        INNER JOIN sys.columns AS c
        ON dc.parent_object_id = c.[object_id]
        AND dc.parent_column_id = c.column_id
        AND dc.is_ms_shipped = 0;
    END


    IF @CheckConstraints = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- CheckConstraints ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' 
            + REPLACE(cc.name, '''', '''''') + ''', @newname = N''CK_' 
            + LEFT(REPLACE(OBJECT_NAME(cc.parent_object_id), '''',''), @TableLimit)
            + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';'
        FROM sys.check_constraints AS cc
        INNER JOIN sys.columns AS c
        ON cc.parent_object_id = c.[object_id]
        AND cc.parent_column_id = c.column_id
        AND cc.is_ms_shipped = 0;
    END


    SELECT @sql;


    IF @SELECT_Only = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END