SQL Server Stored procedure

Question

Hi All,

I have around 2000 tables. I have to rename the original table as a _old_todaydate and I have to create new table with the same name of original table without data with the help of SP by passing two input parameters..

Original table: TEST
Rename table shoud be: TEST_Old_todaysdate
New table should be : TEST (without data)

Is it possible to achive above output using SP with two input parameters? If yes, can you please SP code outlines for this.

Answer 1

To process one table, try something like this:

create procedure MyProcedure
    @table_name varchar(100)
as
begin

    declare @s varchar(max) = 
        concat('select * into ', quotename(@table_name + '_Old_' + convert(varchar(max), getdate(), 120)), 
            ' from ', @table_name, ' truncate table ', @table_name)

    exec (@s)

end

Such things will probably not work in case of relationships between tables.

Answer 2

select 'exec sp_rename @objname=' + name + ', @newname=' + replace(name ,'prefixedchars', '')
from sys.tables
where type = 'U'

--sorry cannot test it right now
CREATE PROCEDURE sp_create_tb
@old sysname,
@mounika sysname
AS

EXEC ('select * into '+@mounika +' from '+@old+ ' where 1=2')

Answer 3

Hi @Viorel , Thanks. The above solution will take more time for 2000 tables. I would like to make this process as dynamic (automation). As you can see below, the rename is same for all 2000 tables (*_old_currentdate), and for new table, need to remove *__old_currentdate

1. Original table names: test, test1, test2, test3...test2000
2. rename all table via SP or dynmic SQL in single shot: test_old_currentdate, test1_old_currentdate..etc
3. create new original table with same table structure of point 2 without *_old_currentdate