CODEWITHSUNDEEP
t-sqlsql-server-2012
Ben W
Ben W

Reputation: 37

Rename multiple tables at once

I am needing some sql code that I can use regularly to rename multiple tables at once. The code below is what I would want if I could use the update statement but cannot. Simple as possible.

UPDATE sys.tables 
SET name = SUBSTRING(name, CHARINDEX('PP_', name),LEN(name))
WHERE CHARINDEX('PP_', name) > 0

Upvotes: 0

Views: 1623

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46202

Use sp_rename to rename objects. Below is an example that generates and executes the needed script.

DECLARE @SQL nvarchar(MAX) =
    (SELECT 'EXEC sp_rename ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''', ''' + SUBSTRING(t.name, CHARINDEX('PP_', t.name),LEN(t.name)) + ''';'
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    WHERE CHARINDEX('PP_', t.name) > 0
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');

EXEC sp_executesql @SQL;

To answer the additional question asked in the comments, you can generate a single DROP TABLE statement for these tables using the script below. Note that this method might not work if the tables have foreign key relationships since it doesn't drop in dependency order.

DECLARE @SQL nvarchar(MAX) =
    N'DROP TABLE ' + STUFF((SELECT ',' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    --WHERE CHARINDEX('PP_', t.name) > 0
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,'')
    + N';';

Upvotes: 2

Related Questions