opc.three (4/1/2013)
I use a slightly different method to avoid some of the entitization problems FOR XML brings with it. Technically it is an undocumented method that I know of but I have not had trouble with it since SQL 2000 and I know of some other techniques that leverage it without trouble too:
DECLARE @SQLCmd NVARCHAR(MAX) = N'';-- instead of using FOR XML just append the conjured string from each row to the same variable
SELECT @SQLCmd += 'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10)
FROM sys.tables tab;
-- show the entire variable contents as an XML document...no truncation like with PRINT
SELECT @SQLCmd AS [processing-instruction(query)]
FOR XML PATH(''),
TYPE;
-- when ready, uncomment
--EXEC sys.sp_executesql @SQLCmd;
Yea, but I think I'll stick with the FOR XML Version, the following was done against a database with 5147 tables in sys.tables:
---------- opc.three verison ----------
Beginning execution loop
CPU(ms): 73015 Logical Reads: 45463611 Elapsed(ms): 73495 Reads: 3790 Writes: 481191
CPU(ms): 72468 Logical Reads: 45460066 Elapsed(ms): 73294 Reads: 3983 Writes: 483049
CPU(ms): 73015 Logical Reads: 45458216 Elapsed(ms): 73864 Reads: 3886 Writes: 483049
CPU(ms): 72453 Logical Reads: 45458185 Elapsed(ms): 73498 Reads: 4068 Writes: 483050
CPU(ms): 72437 Logical Reads: 45461626 Elapsed(ms): 73262 Reads: 3731 Writes: 483049
Batch execution completed 5 times.
---------- FOR XML PATH Version ----------
Beginning execution loop
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 531 Reads: 3477 Writes: 1201
CPU(ms): 187 Logical Reads: 79425 Elapsed(ms): 251 Reads: 3470 Writes: 1201
CPU(ms): 203 Logical Reads: 79425 Elapsed(ms): 414 Reads: 3475 Writes: 1201
CPU(ms): 219 Logical Reads: 79425 Elapsed(ms): 262 Reads: 3466 Writes: 1201
CPU(ms): 172 Logical Reads: 79425 Elapsed(ms): 317 Reads: 3457 Writes: 1201
Batch execution completed 5 times.
Test harness:
PRINT '---------- opc.three verison ----------';
go
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
DECLARE @SQLCmd NVARCHAR(MAX) = N'';
-- instead of using FOR XML just append the conjured string from each row to the same variable
SELECT @SQLCmd += 'EXEC sys.sp_rename ' + --
'@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --
'@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --
'@objtype = N''OBJECT'';' + -- OBJECT
NCHAR(13) + NCHAR(10)
FROM sys.tables tab;
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO 5
set nocount on;
PRINT '---------- FOR XML PATH Version ----------';
go
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @Bitbucket sysname; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
declare @SQLCmd nvarchar(max);
select
@SQLCmd = stuff((select char(13) + char(10) +
'exec sp_rename @objname = N''' + schema_name(tab.schema_id) + '.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) + '.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''
from sys.tables tab
for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO 5