Just Code‎ > ‎

TSQL - Find keys and constraints that was automatically named by SMSS

posted Aug 24, 2011, 2:37 AM by Peter Henell   [ updated Aug 24, 2011, 5:58 AM ]
Autogenerated names are ugly and will probably stride against your naming conventions. Use this script to find them all


select 
        TABLE_SCHEMA ,
        TABLE_NAME ,
        CONSTRAINT_NAME
         from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where CONSTRAINT_NAME like '%[_][_]%' or CONSTRAINT_NAME like '%[0-9]%
This script will generate renaming scripts for constraints where double underscore have been found. Run this if you dare (more or less untested)
SELECT 
    'exec sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + constraint_name + ''', ''' + REPLACE(constraint_name, '__', '_') + ''', ''object'''
FROM 
    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE 
    CONSTRAINT_NAME LIKE '%[_][_]%'
Comments