Technical Article

Changing Primary 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. Select the same database
  7. Execute the script
  8. Validate the primary key names changed.

*Note-

PK_TableName is considered as standard primary key notation

Script works even if PK is referred by FK.

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

SELECT 'exec sp_rename '+''''+'['+S.name+'].['+b.name+']'+''''+','+''''+'PK_'+a.name+''''+','+''''+'OBJECT'+''''+CHAR(10)+'GO' 
FROM sys.objects a 
inner join sys.indexes b ON a.object_id=b.object_id 
INNER JOIN sys.schemas S ON a.schema_id=S.schema_id
WHERE a.object_id =b.object_id and (b.is_primary_key=1 ) and a.type='U' and a.name not like 'sys%'

Rate

3.88 (8)

Share

Share

Rate

3.88 (8)