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'