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

Post date: Aug 24, 2011 9:37:19 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 '%[_][_]%'