Just for fun I wanted to see how a CLR might stack up so I ran it through GROUP_CONCAT against 5000 tables with an average length of ~115 characters:

Version using XML with a couple of the discussed tweaks:

SELECT @SQLCmd = STUFF((SELECT CHAR(13) + CHAR(10) + --

'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'';'

FROM sys.tables tab

FOR XML PATH(''),

TYPE).value('.', 'nvarchar(max)'), 1, 2, '');

Version using CLR:

SELECT @SQLCmd = GroupConcatTest.dbo.group_concat_d(N'EXEC sys.sp_rename ' + --

N'@objname = N''' + SCHEMA_NAME(tab.schema_id) + N'.' + tab.name + N''', ' + --

N'@newname = N''' + tab.name + N'_' + @dt + N''', ' + --

N'@objtype = N''OBJECT''', N';' + NCHAR(13) + NCHAR(10))

FROM sys.tables tab;

---------- opc.three CLR version ----------

SQL Server Execution Times:

CPU time = 140 ms, elapsed time = 240 ms.

---------- FOR XML PATH Version ----------

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 108 ms.