Sql-server – How to rename a table, but also all FK etc. references to that table

sql serversql-server-2012

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?

Best Answer

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

Related Solutions

Sql-server – How to determine what is using a particular resource (table, view, or function, etc) on the SQL Server

I did a DuckDuckGo.com search for the like_i_sql_unicode_string since there appears to be no documentation that describes it included with SQL Server, and came across this Connect item, https://connect.microsoft.com/SQLServer/feedback/details/699053/sql-batch-completed-event-returns-0-for-row-count#details - it contains a sample extended event in the "steps to reproduce" section of the Connect item. In the sample extended event is this text, which wraps the search terms in question in the standard SQL Server wildcard, %.

 WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%sys.objects%')))

I modified my extended event definition like:

DROP EVENT SESSION FindCaller ON SERVER;

CREATE EVENT SESSION [FindCaller] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.nt_username
        , sqlserver.session_id
        , sqlserver.sql_text
    )
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ObjectInQuestion%'))
)
WITH 
(
    MAX_MEMORY=4096 KB
    , EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
    , MAX_DISPATCH_LATENCY=30 SECONDS
    , MAX_EVENT_SIZE=0 KB
    , MEMORY_PARTITION_MODE=NONE
    , TRACK_CAUSALITY=OFF
    , STARTUP_STATE=OFF
);

Now, when I 'Watch Live Data' I see the culprit statements, along with the actual SQL text being executed, the name of the application, the client machine name, etc. Quite useful.

The take-home for me is the Extended Event GUI needs better documentation!

Sql-server – How to write a query which finds all circular references when a table references itself

This calls for a recursive CTE:

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX))
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId <> 0;

See it in action here: SQL Fiddle

Update:

Added distance to be able to exclude all self cycles (see ypercube's comment):

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path, 0 Distance
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)), C.Distance + 1
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId <> 0
  AND R.Distance > 0;

SQL Fiddle

Which one you should use depends on your requirement.

Best Answer

Related Solutions

Sql-server – How to determine what is using a particular resource (table, view, or function, etc) on the SQL Server

Sql-server – How to write a query which finds all circular references when a table references itself

Related Question