|
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)
|
