January 10, 2007 at 10:04 am
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
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
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
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
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
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