Erin Ramsay (4/1/2013)
AndrewSQLDBA (4/1/2013)
SQL Server does not Loop thru rows. SQL Server is Set Based.Please do not indicate that a database loops thru rows.
Andrew SQLDBA
🙂
Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.
Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..
When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.
Okay, how about this:
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,'');
PRINT @SQLCmd;
--exec sp_executesql @SQLCmd;