SP_RENAME

Store Procedure to Rename certain objects in SQL Server

Applies to

• SQL Server

• Azure Synapse Analytics

The SP_RENAME system store procedure can be emulated in Snowflake in certain scenarios. In general, the equivalent is the EXECUTE IMMEDIATE using a dynamic statement with the ALTER TABLE and the original parameters.

Translation Examples for Tables

Source

IN -> SqlServer_01.sql

EXEC sp_rename 'TABLE1', 'TABLENEW1'

Output

OUT -> SqlServer_01.sql

EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 RENAME TO TABLENEW1';

Source

IN -> SqlServer_02.sql

DECLARE @varname1 nvarchar(50) = 'previous_name'
DECLARE @varname2 nvarchar(50) = 'newer_name'
EXEC sp_rename @varname1, @varname2

Output

OUT -> SqlServer_02.sql

DECLARE
VARNAME1 VARCHAR(50) := 'previous_name';
VARNAME2 VARCHAR(50) := 'newer_name';
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || :VARNAME1 || ' RENAME TO ' || :VARNAME2;
END;

Translation Examples for Columns

Source

IN -> SqlServer_03.sql

EXEC sp_rename 'sample_BACKUP_2.column_old', 'column_new', 'COLUMN'
EXEC sp_rename 'database1.sample_BACKUP_3.column_old', 'column_new', 'COLUMN'

Output

OUT -> SqlServer_03.sql

EXECUTE IMMEDIATE 'ALTER TABLE sample_BACKUP_2 RENAME COLUMN column_old TO column_new';

EXECUTE IMMEDIATE 'ALTER TABLE database1.sample_BACKUP_3 RENAME COLUMN column_old TO column_new';

Source

IN -> SqlServer_04.sql

DECLARE @oldColumnName nvarchar(50) = 'previous_name'
DECLARE @newColumnName nvarchar(50) = 'newer_name'
DECLARE @tableName nvarchar(50) = 'TABLE'
EXEC sp_rename @objname = @tableName + '.' + @oldColumnName, @newname = @newColumnName, @objtype = 'COLUMN';

Output

OUT -> SqlServer_04.sql

DECLARE
OLDCOLUMNNAME VARCHAR(50) := 'previous_name';
NEWCOLUMNNAME VARCHAR(50) := 'newer_name';
TABLENAME VARCHAR(50) := 'TABLE';
BEGIN
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0075 - TRANSLATION FOR BUILT-IN PROCEDURE 'SP_RENAME' IS NOT CURRENTLY SUPPORTED. ***/!!!
EXEC sp_rename OBJNAME = :TABLENAME || '.' || :OLDCOLUMNNAME, NEWNAME = :NEWCOLUMNNAME, OBJTYPE = 'COLUMN';
END;

Related EWIs

1. SSC-EWI-TS0075: Translation for Built-In Procedure Is Not Currently Supported.