Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to e.g. rename my Applicant table to ApplicantProfile, as applicant is an abstract entity that has a profile. The profile is not the applicant.

Simply renaming the table is hopelessly inadequate because there are qillions of relationships to Applicant. My closest solution is scripting the whole DB, and doing a search and replace, but this seems risky, as I have fields like ApplicantId that mustn't be renamed. I can't do a whole word only, because I have names like `PK_Applicant'.

Is there a known method or tool I can use for this onerous task?

share|improve this question

migrated from serverfault.com Jan 18 '13 at 11:37

This question came from our site for system and network administrators.

    
I believe you might be able to do this through SSDT, and it may handle all of that through a rename/refactor. –  Thomas Stringer Jan 18 '13 at 14:23
1  
If you're able to select the object names using the DMVs, you can use dynamic SQL to call sp_rename. This will let you rename objects in-place instead of having to drop and recreate everything. –  Jon Seigel Jan 18 '13 at 14:34

1 Answer 1

You should be able to generate the appropriate rename scripts using this, and adjust it to whatever naming convention you choose; I've used this to ensure that the names use the current table and column names, so the where clause currently finds names that don't follow that convention.

SELECT
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME,
    'FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME,
    'sp_rename ''' + C.CONSTRAINT_NAME + ''', ' + '''FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME + '''' + char(13) + char(10) + 'GO' + CHAR(13) + CHAR(10)
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN (
        SELECT i1.TABLE_NAME, i2.COLUMN_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
        ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE
    C.CONSTRAINT_NAME != 'FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME
ORDER BY 
    PK.TABLE_NAME, FK.TABLE_NAME
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.