Post reply

Altering Identical Trigger in many databases.

  • January 10, 2007 at 10:04 am

    #171035

    I need to make a change to a trigger that exists in 500 identical databases.  It is a minor change of changing a datatype from varchar(200) to varchar(2000).  How can I do this in a script?

    Thanks in advance for any advice.

    Keith

  • January 10, 2007 at 10:41 am

    #682466

    This should get you started.

    EXEC sp_msForEACHDB 'IF EXISTS (Select * from ?..SysObjects WHERE Name = ''TriggerName'')

    BEGIN

    USE ?

    ;

    ALTER TRIGGER script

    END'

  • January 10, 2007 at 11:41 am

    #682504

    How can I get around the fact that sp_msforeachdb will only accept 2000 characters.  My trigger script contains more characters than that.  Also, what do I do about constant data enclosed in quotes.

    Thanks

  • January 10, 2007 at 12:05 pm

    #682514

    Use OSQL or SQLCMD command with cursor or while loop...

    SELECT 'EXEC XP_CMDSHELL "OSQL -S'+@@servername+' -d '+NAME+' -i c:\triggerfile' FROM MASTER..SYSDATABASES WHERE DBID >6

    Take the output from the above command run it...if it is sql 2000 you use xp_executeresultset command but not in 2005.

     

    MohammedU
    Microsoft SQL Server MVP

  • January 11, 2007 at 7:06 am

    #682672

    If you're considering the use of 3rd party tools, look at SQL Farms- they have a tool with which you can connect to all databases/server in parallel and run any type of SQL scripts, so you can make the updates on all your 500 databases.

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

  • January 12, 2007 at 11:12 am

    #683005

    You can also create a stored procedure that takes varchar(8000) as param and executes it and then use it in the for each statement.

Viewing 6 posts - 1 through 5 (of 5 total)

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