DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to rename all tables in the database?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate table and column renaming:

-- SQL Server rename table command - SQL Server sp_rename system procedure

USE AdventureWorks2008R2;

SELECT * INTO Sales.ST FROM Sales.SalesTerritory;     -- Create test table

EXEC sp_rename 'Sales.ST', 'SalesTerr';               -- T-SQL rename table

----------

 

-- SQL Server rename column - change column name in table

EXECUTE sp_rename N'Sales.SalesTerr.SalesLastYear', N'LastYearSales', 'COLUMN'

/* MESSAGE: Caution: Changing any part of an object name could break scripts

   and stored procedures.  */

------------

------------

-- Switching tables with sp_rename to make staging table production

------------

USE tempdb;

GO

CREATE TABLE Alpha( i int, c varchar(32));

CREATE TABLE stageAlpha( i int, c varchar(32));

INSERT Alpha VALUES (5, 'Elvis Presley');

-- Build / populate staging table while production table is in usage

INSERT stageAlpha VALUES ( 111, 'Katharine Hepburn')

INSERT stageAlpha SELECT * FROM Alpha

 

-- Switch staging table to become production table

BEGIN TRANSACTION

EXEC sp_rename Alpha, zzzAlpha

EXEC sp_rename stageAlpha, Alpha

COMMIT TRANSACTION

 

-- At a future date like 3 months later

DROP TABLE zzz
------------

 

-- sp_rename usage with dynamic SQL

use tempdb;

go

create table Delta ( i int); insert Delta VALUES (999);

declare @NewTableName sysname                           -- Dynamic table name

set @NewTableName = 'Gamma'

exec ('sp_rename ''Delta'',' + @NewTableName + '')      -- Dynamic SQL

select * from Gamma

go

drop table Gamma

----------

The following SQL Server 2005/2008 T-SQL script generates rename scripts for all tables for prefixing with "TBL". The result window should be in the "text" mode:

 

select 'exec sp_rename '''+schema_name(schema_id)+'.'+name+''', '''+

'TBL'+name+''''+char(10)+'go'

from sys.objects

where type = 'U'

order by name

-- Partial results

-- exec sp_rename 'Person.PersonPhone', 'TBLPersonPhone'

----------

Related articles:

SQL SERVER How to Rename a Column Name or Table Name

sp_rename (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE