Renaming poorly named constraints in MS SQL

I recently found myself in a situation where I was working on tuning SQL Server performance in a SQL Server 2008 R2 environment. What made this environment different than other environments that I had worked on in the past, was that the environment had ~400 databases that were all (essentially) identical in structure. The company sells a remote-hosted solution, so whenever a new client comes on board they simply spin each client off their own appropriately structured database that they can then begin filling with their own data.

In the process of tuning the SQL performance, there were many instances where I needed to make changes to Primary and Foreign keys, unfortunately many of the keys did not have the same name in each database; because when the tables were originally created the constraints were not explicitly named; and were instead named by SQL Server itself.

Consider these simple examples.

--Implicit
CREATE TABLE dbo.Users
(
	id_Users int IDENTITY(1,1) NOT NULL PRIMARY KEY
	, UserName nvarchar(20) NOT NULL
	, UserPassword nvarchar(20) NOT NULL
);

CREATE TABLE dbo.UserLoginHistory
(
	id_Users int NOT NULL FOREIGN KEY REFERENCES dbo.Users (id_Users)
	, LoginTime datetime2 NOT NULL
);
CREATE CLUSTERED INDEX cidx_dbo_UserLoginHistory ON dbo.UserLoginHistory (id_Users);

select name, 'pk' AS [type] FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('dbo.Users')
union all
select name, 'fk' AS [type] FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.UserLoginHistory');

And here is an example of explicitly defined primary and foreign keys:

--Explicit
CREATE TABLE dbo.Users
(
	id_Users int IDENTITY(1,1) NOT NULL
	, UserName nvarchar(20) NOT NULL
	, UserPassword nvarchar(20) NOT NULL
	, CONSTRAINT pk_dbo_Users PRIMARY KEY CLUSTERED (id_Users)
);

CREATE TABLE dbo.UserLoginHistory
(
	id_Users int NOT NULL
	, LoginTime datetime2 NOT NULL
	, CONSTRAINT fk_dbo_UserLoginHistory_dbo_Users FOREIGN KEY (id_Users) REFERENCES dbo.Users (id_Users)
);
CREATE CLUSTERED INDEX cidx_dbo_UserLoginHistory ON dbo.UserLoginHistory (id_Users);

select name, 'pk' AS [type] FROM sys.key_constraints WHERE parent_object_id = OBJECT_ID('dbo.Users')
union all
select name, 'fk' AS [type] FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.UserLoginHistory');

And the results from each:

Implicit
type	name
pk	PK__Users__B3F21DFE5BED93EA
fk	FK__UserLogin__id_Us__5ECA0095

Explicit
type	name
pk	pk_dbo_Users
fk	fk_dbo_UserLoginHistory_dbo_Users

When you allow SQL Server to name your constraints for you, it will generate a name that is partially based on the object name, then append a set of random hex characters to the end. So in the case of the system I was working in, this resulted in 400 different names for the same constraints. So any maintenance I wanted to perform against those constraints would first have to dynamically identify the name of the constraint before the logic could proceed. Further, any future maintenance would require the same level of effort. I opted to create these two simple scripts which would name all Primary and Foreign keys based on a defined naming convention, thus bringing all of the databases into sync and making future maintenance much easier, so long as future development does not use implicit constraint creation techniques.

-------------------------------------------
--Rename all system generated primary keys using the following format
-- pk_schema_tablename
-------------------------------------------
DECLARE @oldname nvarchar(776)
        , @newname sysname;

DECLARE key_curse CURSOR FAST_FORWARD FOR (SELECT
                                                '[' + SCHEMA_NAME(kc.schema_id) + '].[' + kc.name + ']' AS [oldname]
                                                , 'pk_' + SCHEMA_NAME(kc.schema_id) + '_' + OBJECT_NAME(kc.parent_object_id) AS [newname]
                                                FROM sys.key_constraints kc
                                                WHERE
                                                    kc.type = 'PK'
                                                    AND kc.name <> 'pk_' + SCHEMA_NAME(kc.schema_id) + '_' + OBJECT_NAME(kc.parent_object_id));

OPEN key_curse

    FETCH NEXT FROM key_curse INTO @oldname, @newname;

    WHILE(@@FETCH_STATUS = 0)
    BEGIN

        print substring(@oldname, charindex('.', @oldname) + 1, len(@oldname)) + '     ' + @newname;
        EXEC sp_rename @objname = @oldname, @newname = @newname, @objtype = 'OBJECT';

        FETCH NEXT FROM key_curse INTO @oldname, @newname;
    END

CLOSE key_curse;
DEALLOCATE key_curse;
GO

-------------------------------------------
--Rename all system generated foreign keys using the following format
-- fk_schema1_tablename1_schema2_tablename2
-- where schema/table 1 = the ReferencING table
-- and schema/table 2 = the ReferencED table
-------------------------------------------
DECLARE @oldname nvarchar(776)
		, @newname sysname;

DECLARE key_curse CURSOR FAST_FORWARD FOR (select
											'[' + SCHEMA_NAME(o_ing.schema_id) + '].[' + fk.name + ']' AS [oldname]
											, 'fk_' + SCHEMA_NAME(o_ing.schema_id) + '_' + o_ing.name + '_' + SCHEMA_NAME(o_ed.schema_id) + '_' + o_ed.name AS [newname]
											FROM sys.foreign_keys fk
											JOIN sys.objects o_ing ON o_ing.object_id = fk.parent_object_id
											JOIN sys.objects o_ed ON o_ed.object_id = fk.referenced_object_id
											WHERE
												fk.name <> 'fk_' + SCHEMA_NAME(o_ing.schema_id) + '_' + o_ing.name + '_' + SCHEMA_NAME(o_ed.schema_id) + '_' + o_ed.name); --The format of our standard key

OPEN key_curse

	FETCH NEXT FROM key_curse INTO @oldname, @newname;

	WHILE(@@FETCH_STATUS = 0)
	BEGIN

		print SUBSTRING(@oldname, charindex('.', @oldname) + 1, len(@oldname)) + '     ' + @newname;
		EXEC sp_rename @objname = @oldname, @newname = @newname, @objtype = 'OBJECT';

		FETCH NEXT FROM key_curse INTO @oldname, @newname;
	END

CLOSE key_curse;
DEALLOCATE key_curse;
GO

There was already a system in place to apply a SQL script to every client database, so there was no need to employ sp_MSForeachdb (Just Kidding!) Aaron Bertrand’s sp_foreachdb.

I haven’t tested this script against any other versions of MSSQL, but I think it should work fine on anything >= SQL Server 2005.

In closing, to all you developers out there, please take the time to explicitly name your constraints!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s