Same changes on many databases
-
My company's structure has each client in a separate db. All db's have the exact same structure. I have several changes such as adding a column to a table and changing the the referencing views and procs that I need to make in all databases. I have 6 scripts in all and a couple of the procs are very long. I already have a script that will iterate through the databases and select data from each database into a temp table. I do this by using the EXEC(sql) statement, but that function is limited to 2000 chars and my procs are well over that.
Does anyone have any suggestions on how I can do this?
Thanks alot.
Keith
-
Hi Keith,
if you use sp_executesql then you can use an nvarchar(4000) - would that be long enough for your needs?
Alternatively you could manage your changes using one of the many tools on the market - my company makes one called DB Ghost and it can help maintain a proper audit trail of all changes whilst allowing you to easily and seamlessly propagate those changes to other databases - click on my signature link if you're interested
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com -
You can go beyond 4k if you use exec(), you just have to do something like exec (@sql1 + @sql2 + ....).
Compare (diff) tools are a good way to generate the change script, you could easily apply it to multiple db's by just changing the 'Use db' at the top. In the past I used a home grown tool that would actually copy the script over and over for every db I had selected, inserting the appropriate use statement, then I could run from QA (or tool of choice) and when done just check the exec results to see if anything had gone wrong. Only advantage was that I could save that change script and easily see what db's I had applied it to.
-
Save all your changes in a script file, then cursor through your DBs calling OSQL using xp_cmdshell.
-
Assuming that all of your stored procedures and changes are already in some type of version control system.....they should already be in seperate files.
Write up a script that loops thru your databases ( select name from sys.databases) and generates a Use Database statement for each client databse.
Then use osql, and execute the use database statement and then call in the file for execution.
Hope that this helps
Eric
-
If you're considering the use of 3rd party tools, you can look at SQL Farms- they have a tool that allows you to run the same change script against all databases and servers in a single click.
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
Wound up going with Schleep's suggestion. I was a able to create query to actually create the OSQL commands for me then I just executed the OSQL commands. This worked great. I applied 15 object changes to 552 databases very quickly.
Thanks alot for all of your advice. I will definitely keep all of your suggestions in mind
Keith
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply