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.
You said that like it would be challenging. Quite simple in fact.
declare @MyQuery nvarchar(max) =
(
select 'exec sp_rename [' + name + '], [' + name + '_' + convert(varchar(20), getdate(), 112) + '];'
from sys.tables
for xml path('')
)
exec sp_executesql @MyQuery
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.
Yes they are horribly overused. Cursor are very important and serve as an invaluable tool when doing maintenance. They are not always evil but often they only require a change in the way we think about data. 😉
--EDIT--
It seems that while I was writing Lynn posted another version that is similar to mine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/