Avoiding a Cursor

Question

Post reply

Avoiding a Cursor

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

five_ten_fiftyfold Ten Centuries Points: 1161 More actions · Answer 1

five_ten_fiftyfold

Ten Centuries

Points: 1161

February 29, 2012 at 7:14 am

#1453146

I guess sometimes you need to dance with the Devil 🙂

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

five_ten_fiftyfold (2/29/2012)
I guess sometimes you need to dance with the Devil 🙂

Again, nope. You could use FOR XML to generate your SQL dynamically then execute it.

e.g.

DECLARE @SQL AS NVARCHAR(MAX);

SELECT @SQL = STUFF(REPLACE(CAST((

SELECT ';' + CHAR(13) + CHAR(10) + 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME LIKE '%71%'

FOR XML PATH('')) AS NVARCHAR(MAX)),';&#x0D',''),1,2,'');

PRINT @SQL;

--EXECUTE sp_executesql @SQL;

Creates the following on one of my test servers: -

DROP TABLE [dbo].[TC71_MangledWords];

DROP TABLE [dbo].[TC71_Dictionary]

If I then run the commented out code instead of the PRINT, those two tables would be dropped.

--Edit--

I never learn. . . the forums have replaced "& # x 0 D ;" (no spaces or quotations) with a line break in the code block above. If you add "& # x 0 D ;" (no spaces or quotations) back into the replace, just before the CHAR(13) + CHAR(10) at the end, then it'll work but otherwise it won't.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

five_ten_fiftyfold (2/29/2012)
I guess sometimes you need to dance with the Devil 🙂

Nah... not for something like this. Cadavre has the right stuff on this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Possinator Hall of Fame Points: 3422 More actions · Answer 4

Lol. The right "stuff". You made a punny. 😛

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)