Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

equivalent of spool Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 10:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
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
Posted Monday, January 9, 2012 10:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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 »

Add to briefcase

Permissions Expand / Collapse