2

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?

2
  • I believe you might be able to do this through SSDT, and it may handle all of that through a rename/refactor. Commented Jan 18, 2013 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
    Commented Jan 18, 2013 at 14:34

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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