equivalent of spool

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » equivalent of spool

equivalent of spool

Rate Topic

Display Mode

Topic Options

Author

Message

newbieuser

Posted Monday, January 09, 2012 10:45 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 306, Visits: 722

Hi friends,

I need to drop and recreate check constraints in a few databases and would need to script this so it can be run over different databases at customer's end. Below is the sql I need to run to drop check constraints

SELECT
'ALTER TABLE ' +
QuoteName(OBJECT_NAME(so.parent_obj)) +
CHAR(10) +
' DROP CONSTRAINT ' +
QuoteName(CONSTRAINT_NAME)
FROM
INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN sys.sysobjects so
ON cc.CONSTRAINT_NAME = so.[name]

In Oracle, I can spool the results of the above sql to a file and run the file to drop the constraints.. Is there a way to do the same in SQL Server? Please give me your thoughts

Thank you

Post #1232659

GSquared

Posted Monday, January 09, 2012 10:51 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

You could output to a file and run that. Any reason to do so instead of just executing it as dynamic SQL?

If you really insist on the output a file step, you could do the whole thing in a PowerShell script, or something like that. Or write that query as a View and use BCP to output it then use PowerShell to run it.

Easier would be to create a cursor for that Select statement, then step through it and execute each line as a command.

Or if you can be sure it'll all fit in one command, use the For XML Path('') trick to turn it into a single statement and execute it all at once. I'd use a cursor, though. Easier and more reliable.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1232667

« Prev Topic | Next Topic »

Permissions