Technical Article

Changing Foreign key names to standard naming convention across the DB

,

  1. Copy the script into Query window
  2. Select the appropriate database
  3. Select "Query Result to Text" from the Query Menu
  4. Execute the script
  5. Copy the result/ output script in the query window
  6. If there are multiple foreign keys (More than one) from same child table to the same Master table; change the key name to FK1_, FK2_ etc. This option is better than adding column names in the key name.
  7. Select the same database
  8. Execute the script
  9. Validate the foreign key names changed.

*Note-

FK_ChildTableName_Parent_Table_Name is considered as standard primary key notation

--Vidhyadhar Pandekar
--vidya_pande@yahoo.com
--10th feb,2010
-- Script can be used to standardize the Foreign key names across the database to FK_childTableName_ParentTableName


 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'

Rate

3.75 (4)

Share

Share

Rate

3.75 (4)