Rename some Tables to Table Name + DDMMYYYY

问题:

I am importing some tables from one SQL Server to another, every night and the table should have a DDMMYYYY sufix at the destination. I could not think of a way to change the name during import process and thought of renaming it after import. I am stuck here too as I am not able to get SP_RENAME to work in the format SP_RENAME 'TABLE_NAME' 'TABLE_NAME+DDMMYYYY'.

回答1:

Well, first of all, it really sounds like you should be loading this tables on one table and use another column for storing the date. Nevertheless, you could use dynamic SQL for what you want:

DECLARE @Date NVARCHAR(8), @Table NVARCHAR(500), @Query NVARCHAR(2000)

SELECT @Date = CONVERT(VARCHAR(8),GETDATE(),112)
SET @Table = 'TABLE_NAME'
SET @Query = 'EXEC sp_rename '''+@Table+''','''+@Table+@Date+''''

EXEC(@Query)

Oh, and take a look at this link to read about dynamic SQL on SQL Server.

回答2:

Execute this script in SQL Server Management Studio (Ctrl+T (Text Output), F5 (Execute)) to generate another T-SQL script:

SET NOCOUNT ON;
GO

DECLARE @StartDate DATETIME,@EndDate DATETIME;
SELECT  @StartDate='2013-07-10T00:00:00',
        @EndDate='2013-07-23T00:00:00';

PRINT 'BEGIN TRANSACTION;'

SELECT  'EXEC sp_rename '
        +''''
        +QUOTENAME(s.name)+'.'+QUOTENAME(t.name)
        +''','''
        +QUOTENAME(t.name+STUFF(STUFF(CONVERT(VARCHAR(25),t.create_date,103),3,1,''),5,1,''))
        +''', ''OBJECT'''
FROM    sys.tables t
JOIN    sys.schemas s ON t.schema_id=s.schema_id
WHERE   t.create_date BETWEEN @StartDate AND @EndDate
--AND   t.name LIKE '%pattern%'

PRINT 'ROLLBACK;'
PRINT '-- COMMIT;'

SET NOCOUNT OFF;
GO

Example output/script generated (Message panel):

BEGIN TRANSACTION;    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC sp_rename '[dbo].[NodesOrder]','[NodesOrder19072013]'
EXEC sp_rename '[dbo].[RateTable]','[RateTable19072013]'
EXEC sp_rename '[dbo].[A]','[A22072013]'
EXEC sp_rename '[dbo2].[A]','[A22072013]'
EXEC sp_rename '[dbo].[ConstraintTest]','[ConstraintTest17072013]'
ROLLBACK;
-- COMMIT;

Now, execute this script (Ctrl+C, Ctrl+V, F5).