Standardise PK and FK Names

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 11 May 2010

Description

When creating primary or foreign keys using TSQL or the GUI, there is currently no way of enforcing a naming convention at creation time - the system automatically generates a name. A separate command must be run afterwards to rename the constraint.

Most ER Design tools offer the option of providing or enforcing a naming convention at design time, before scripts are generated and rolled out, but not every DBA shop can afford these often pricy additions to the arsenal.

I find it very helpful, not only on a personal level, but for development teams, to have a standard naming convention that allows easy and immediate understanding of the PK/FK relationship scope and direction

The following two scripts will generate TSQL commands for changing all PK and FK names in a database.

They are written to enforce the following standard:

- Primary Key: PK_<TableName>
- ForeignKey: FK_<ParentTableName>_<ChildTableName>

but may be easily altered to whatever standard that you use.

Code

Standardise Primay Key Names:

SELECT

'EXEC sp_rename '

+ '''['

+ CCU.TABLE_SCHEMA

+ '].['

+ CCU.CONSTRAINT_NAME

+ ']'',''PK_'

+ CCU.TABLE_NAME

+ ''',''OBJECT'''

+ CHAR(10)

+ 'GO'

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

ON CCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME

WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'

Standardise Foreign Key Names:

SELECT

'EXEC sp_rename '

+ '''['

+ CCU.TABLE_SCHEMA

+ '].['

+ CCU.CONSTRAINT_NAME

+ ']'',''FK_'

+ CCU.TABLE_NAME

+ '_'

+ TC1.TABLE_NAME

+ ''',''OBJECT'''

+ CHAR(10)

+ 'GO'

FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

ON CCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

ON TC.CONSTRAINT_NAME=RC.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC1

ON RC.UNIQUE_CONSTRAINT_NAME=TC1.CONSTRAINT_NAME

WHERE TC.CONSTRAINT_TYPE='FOREIGN KEY'