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