Altering Identical Trigger in many databases.

  • 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

  • This should get you started.

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

    BEGIN

    USE ?

    ;

    ALTER TRIGGER script

    END'

  • 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

  • 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

  • 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.

  • 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