How can I synchronize views and stored procedures between SQL Server databases?

Question

I have a 'reference' SQL Server 2005 database that is used as our global standard. We're all set up for keeping general table schema and data properly synchronized, but don't yet have a good solution for other objects like views, stored procedures, and user-defined functions.

I'm aware of products like Redgate's SQL Compare, but we don't really want to rely on (any further) 3rd-party tools right now.

Is there a way to ensure that a given stored procedure or view on the reference database, for example, is up to date on the target databases? Can this be scripted?

Edit for clarification: when I say 'scripted', I mean running a script that pushes out any changes to the target servers. Not running the same CREATE/ALTER script multiple times on multiple servers.

Any advice/experience on how to approach this would be much appreciated.

Answer 1

1) Keep all your views, triggers, functions, stored procedures, table schemas etc in Source Control and use that as the master.

2) Failing that, use your reference DB as the master and script out views and stored procedures etc: Right click DB, Tasks->Generate Scripts and choose your objects.

3) You could even use transactional replication between Reference and Target DBs.

I strongly believe the best way is to have everything scripted and placed in Source Control.

Answer 2

I use (and love) the RedGate tools, but when Microsoft announced Visual Studio 2010, they decided to allow MSDN subscribers who get Visual Studio 2008 Team System to also get Visual Studio 2008 Database Edition (which has a schema compare tool).

So if you or your organization has an MSDN subscription, you might want to consider downloading and installing the Database Edition over your Team System to get all the features now.

More details at http://msdn.microsoft.com/en-us/vsts2008/products/cc990295.aspx

Answer 3

You can use the system tables to do this.

For example,

select * from sys.syscomments

The "text" column will give you all of the code for the store procedures (plus other data).

It is well worth looking at all the system tables and procedures. In fact, I suspect this is what RedGate's software and other tools do under the hood.

I have just myself begun experimenting with this, so I can't really be specific about all the gotcha's and what other system tables you need to query, but this should get you started.

Also see:

http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-proc

which is slightly different question than yours, but related.

Answer 4

Take a look at ScriptDB on Codeplex (http://www.codeplex.com/ScriptDB)

It is a console C# app that creates scripts of the SQL Database objects using SMO. You can use that to compare scripts generated on two servers. Since its open source, adjust it if you need it.

Timur