Friday, May 02, 2008

How to rename database objects to comply with naming conventions

We have guidelines for the naming conventions of our database objects.  It's pretty much the common pattern you see all over the place. For example, we use the following convention for default constraints:

DF_TableName_ColumnName, such as DF_Student_School

Pinal Dave has great set of database coding standards on his site, more in-depth than ours actually.  We have found that using coding standards makes the database schema easier to read and makes it easier to perform schema updates.  If you don't explicitly name a default, SQL Server will name it for you.  And it wont be pretty or consistent across databases.  We have our own tool for pushing out schema updates to our customers and it assumes the object names will be the same for each database.

When we submit our schema updates internally, we usually catch any deviation from our naming conventions.  It's not a perfect process and every now and then, something slips through the cracks.  We then correct the schema update to use the appropriate naming convention.  if we have been using the schema changes internally, we may have some databases that don't match the published schema for the object names.  When that happens, we run a simple T-SQL script that cleans house.  The following T-SQL will scan the database for default constraints that do not match our naming conventions and rename the ones that it finds.

DECLARE @OldName nvarchar(512)
DECLARE @NewName nvarchar(512)
DECLARE @OldToNewName nvarchar(512)

-- We use a table variable to contain the list of objects to be renamed.
DECLARE @FixSchema TABLE(
sys_OldName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
sys_NewName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

-- Get the list of current column defaults
-- The OldName column represents the current name of the default
-- The NewName column represents the name of the default using
-- our naming convention
INSERT INTO @FixSchema(sys_OldName, sys_NewName)
select o.name AS OldName
,'DF_' + Object_Name(o.parent_obj) + '_' + c.Name AS NewName
FROM sysobjects o
JOIN syscolumns c ON o.parent_obj = c.id AND c.cdefault = o.id
WHERE o.type = 'D'

-- Declare cursor on this table variable to access on the non-compliant
-- constraints
DECLARE AddDrop CURSOR FOR
SELECT sys_OldName, sys_NewName
FROM @FixSchema
where sys_OldName <> sys_NewName

OPEN AddDrop

-- Loop through the list of defaults where the default name is not
-- strongly typed
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Assenble a T-SQL command that can be executed dynamically
-- to rename the constraint
SELECT @OldToNewName = 'sp_rename ' + QUOTENAME(@OldName,'''') +
', ' + QUOTENAME(@NewName,'''') + ', ' + QUOTENAME('object','''')

-- Print the command to be executed. Useful for seeing
-- what is being done
PRINT @OldToNewName

-- Execute the T-SQL to rename the object. If the EXEC line is
-- commented out, the code will display what would be changed
-- with making any actual changes to the schema
EXEC(@OldToNewName)
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName
END

-- Close and deallocate the cursor
CLOSE AddDrop
DEALLOCATE AddDrop

I usually use the INFORMATION_SCHEMA views to peek at the table and column structures.  The INFORMATION_SCHEMA.COLUMNS view will tell which columns have default constraints, but not the name of the constraint. In this case I needed to access the sysobjects table to retrieve the name of the constraint.  The same type of code can be used to rename other objects like check constraints.

8 comments:

  1. Great tool! I had to update it slightly to include the schema name since MY default schema is not the same as the default schema for the part of the database that I wanted to run this on.

    But it was great otherwise.

    ReplyDelete
  2. By the way, I personally have trouble reading code that has gratuitous table aliases.

    Isn't the following much easier for a human to read and understand, than the original?

    INSERT INTO @FixSchema(sys_OldName, sys_NewName)
    select sysobjects.name AS OldName, 'DF_' + Object_Name(sysobjects.parent_obj) + '_' + syscolumns.Name AS NewName,
    sysobjects.type
    FROM sysobjects
    JOIN syscolumns ON sysobjects.parent_obj = syscolumns.id AND syscolumns.cdefault = sysobjects.id
    WHERE sysobjects.type = 'D'

    ReplyDelete
  3. Excellent script!!!

    Thanks mate - you've saved me the hassle of reinventing the wheel

    ReplyDelete
  4. You use two FETCH NEXT statements wich are exactly the same. By writing the loop a little different, only one FETCH NEXT is necessary:

    WHILE (1=1)
    BEGIN
    FETCH NEXT
    FROM AddDrop
    INTO @OldName, @NewName
    IF (@@FETCH_STATUS <> 0) BREAK

    ...
    END

    ReplyDelete
  5. If you use the following code, you can also select tablenames, indexnames and so on to be renamed into @FixTable.

    DECLARE @FixTable TABLE
    (
    sys_OldName [sysname] NOT NULL,
    sys_NewName [sysname] NOT NULL
    )
    INSERT INTO @FixTable (sys_OldName, sys_NewName)
    SELECT o.name AS OldName, (N'DF_' + Object_Name(o.parent_obj) + N'_' + c.Name) AS NewName
    FROM sysobjects o
    JOIN syscolumns c ON o.parent_obj = c.id AND c.cdefault = o.id
    WHERE o.type = 'D' AND
    o.name <> (N'DF_' + Object_Name(o.parent_obj) + N'_' + c.Name)

    DECLARE @OldName NVARCHAR (1024)
    DECLARE @NewName NVARCHAR (1024)
    DECLARE FixCursor CURSOR LOCAL FOR
    SELECT sys_OldName, sys_NewName
    FROM @FixTable

    OPEN FixCursor
    WHILE (1=1)
    BEGIN
    FETCH NEXT FROM FixCursor
    INTO @OldName, @NewName
    IF (@@FETCH_STATUS <> 0) BREAK

    SET @OldName = N'[' + @OldName + N']'
    EXECUTE sp_rename @objname = @OldName,
    @newname = @NewName,
    @objtype = N'OBJECT'
    END
    CLOSE FixCursor
    DEALLOCATE FixCursor

    ReplyDelete
  6. When did SQL developers suddenly begin to think that this WHILE loop:
    WHILE (1=1)
    BEGIN
    FETCH NEXT FROM FixCursor
    INTO @OldName, @NewName
    IF (@@FETCH_STATUS <> 0) BREAK

    is any different than this one:
    WHILE @@FETCH_STATUS = 0
    BEGIN
    FETCH NEXT FROM FixCursor
    INTO @OldName, @NewName
    ...
    END

    Someone somewhere along the line thought that it performed better and wasn't a real cursor. Hogwash, it's still a cursor and still loops each record. It's also harder to follow.

    ReplyDelete
  7. I concur with the last "anonymous". Writing a while/end loop using the syntax "while (1=1)" gains nothing in performance and makes the code harder to read.

    You have gone from a single possible test to exit the loop to two, which increases the complexity of the code without any measurable benefit. The fetch next statement is still being performed for the same number of rows either way.

    At the end of the day, you go with the code that you and your team can read 6 months from now and still understand.

    ReplyDelete
  8. Those two while loops are different, though. @@FETCH_STATUS is undefined if you haven't called fetch yet, so SQL Server could rightfully return any value and your loop wouldn't execute. Also, if you wrote two consecutive loops the second wouldn't execute because @@FETCH_STATUS <> 0 after the first finishes executing.

    "while 1 = 1" is uglier than a "while (true)" in other languages, but this little bit of boilerplate simplifies maintenance if you ever need to change the fetch statement.

    ReplyDelete

Note: Only a member of this blog may post a comment.