Rename the table with Suffix current date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rename the table with Suffix current date

Author

Topic

BPSQL
Starting Member

4 Posts

Posted - 2010-02-26 : 09:49:14

Hi all,
I want to make a stored procedure that renames a table to a new table name with suffix as the current date. I would like the table name to read testdd/mm/yyyy, so far I have the below coding

USE [Test]
go

create procedure [dbo].[renameTest]
@Test varchar(50),
@Test02 varchar(50)
as
set @Test02 = @Test02 +(CONVERT(VARCHAR(8),GETDATE())
EXEC sp_rename @Test, @Test02

Test - old table
Test02 - new table

This does not seem to work as the table name remains the same. Any help on this would be greatly appreciated.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-26 : 10:57:04

Try this:

Create procedure [dbo].[renameTest]
@Test varchar(50),
@Test02 varchar(50)

as
Begin
set @Test02 = @Test02 +(CONVERT(VARCHAR(8),GETDATE(),3))

Exec('sp_rename ''' + @Test +''','''+ @Test02 + '''')

End

Regards,
Bohra

BPSQL
Starting Member

4 Posts

Posted - 2010-02-26 : 11:12:41

Hi
Thanks for your rapid response, this command ran successfully but when I refreshed the database to see if the table name had been updated, it still had the original table name in. Any other suggestions? Many Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-02-26 : 11:18:10

Hi,

If table is not found , it will return an error. If it is found it will rename it.

I tried the sp before posting and its working.

Just try executing the below query and check whether still you see old table name or new name.

select * from sys.sysobjects where xtype ='U' and name like <yourtablename> %

Example
select * from sys.sysobjects where xtype ='U' and name like 'Test%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:15:07

quote:
Originally posted by BPSQL

Hi
Thanks for your rapid response, this command ran successfully but when I refreshed the database to see if the table name had been updated, it still had the original table name in. Any other suggestions? Many Thanks

Is the procedure created in same db as your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 00:52:45

This doesn't need dynamic SQL, surely?

For dd/mm/yyyy you need CONVERT(VARCHAR(10),GETDATE(),103))

But I question the wisdom of putting "/" in table names ...

There is also a syntax error in the original O/P question which would have prevented the SProc from compiling:


set @Test02 = @Test02 +(CONVERT(VARCHAR(8),GETDATE()))


USE [Test]
go

create procedure [dbo].[renameTest]
@Test varchar(50),
@Test02 varchar(50)
as

set @Test02 = @Test02 +CONVERT(CHAR(10), GETDATE(), 103)

PRINT 'Rename [' + COALESCE(@Test, 'NULL') + '] to [' + COALESCE(@Test02, 'NULL') + ']'
IF NOT EXISTS (SELECT * FROM sys.tables WHERE [name] = @Test)
BEGIN
    PRINT 'Table [' + COALESCE(@Test, 'NULL') + '] does not exist'
END
ELSE
BEGIN
    EXEC sp_rename @Test, @Test02
END
GO

exec dbo.renameTest @Test='Test', @Test02='Test02'

BPSQL
Starting Member

4 Posts

Posted - 2010-03-01 : 03:48:06

Thanks Kristen your coding worked perfectly. Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources