Skip to content

SQL Rename Tables, Views and Stored Procedures

April 2, 2012

In this example I am removing “__” from the front of all tables, views and stored procedures.

DECLARE @Cursor CURSOR	
DECLARE @name VARCHAR(50)

SET 
@Cursor = CURSOR FORWARD_ONLY STATIC FOR
    SELECT  name
    FROM    sysobjects
    WHERE   ([type] = 'U'  --Tables
            OR [type] = 'V' --View
            OR [type] = 'P')  --Stored Procedures
            AND SUBSTRING(name, 1, 2) = '__'

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name

WHILE @@FETCH_STATUS = 0 
    BEGIN
        DECLARE @newname AS VARCHAR(50)
        SET @newname =  SUBSTRING(@name,2,99) 
        SELECT @newname
        EXEC sp_rename @name, @newname 

        FETCH NEXT FROM @Cursor INTO @name
    END

CLOSE @Cursor
DEALLOCATE @Cursor
Advertisements

From → SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: