equivalent of spool

  • 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

  • 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

  • Hi,

    Replace the SELECT 'select table_name  with your own select alter table.

    IF OBJECT_ID('TEMPDB..#COMMAND_LIST') IS NOT NULL

    DROP TABLE #COMMAND_LIST

    CREATE TABLE #COMMAND_LIST

    (ID INT IDENTITY,

    COMMAND VARCHAR(200))

    INSERT INTO #COMMAND_LIST (COMMAND)

    SELECT 'select table_name from information_schema.tables where table_name = ''' + table_name + ''''

    from information_schema.tables

    --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]

    DECLARE @LOOP INT,

    @MAX_LOOP INT,

    @SQL VARCHAR(200)

    SELECT @MAX_LOOP = MAX(ID) FROM #COMMAND_LIST

    set @Loop = 1

    WHILE @LOOP <= @MAX_LOOP

    BEGIN

    SELECT @SQL = COMMAND FROM #COMMAND_LIST WHERE ID = @LOOP

    --EXECUTE(@SQL)

    PRINT @SQL

    SET @LOOP = @LOOP + 1

    END

    Jamie Ingram

    We are the pilgrims, master.
    We shall go always, a little further.

Viewing 3 posts - 1 through 3 (of 3 total)

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