Geek Answers Handbook

How to synchronize views and stored procedures between SQL Server databases?

I have a basic SQL Server 2005 database that is used as our global standard. We are all set up to correctly synchronize the overall schema and table data, but so far we have no good solution for other objects, such as views, stored procedures, and user-defined functions.

I know products like Redgate SQL Compare , but we really do not want to rely (further) on third-party tools.

Is there a way to ensure that a given stored procedure or view in a reference database, for example, is updated in the target databases? Could this be written?

Edit for clarification: when I say “script”, I mean running a script that pushes any changes to the target servers. Do not run the same CREATE / ALTER script multiple times on multiple servers.

Any advice / experience regarding how to approach this would be greatly appreciated.

+3
source share
4 answers

You can use system tables for this.

For instance,

select * from sys.syscomments

The text column will provide you with all the code for the storage procedures (plus other data).

It’s good to look at all system tables and procedures. In fact, I suspect that this RedGate software and other tools are doing under the hood.

I just started experimenting with this, so I cannot be specific about all the resulting and other system tables that you need to query, but this should help you get started.

See also:

A query to display SQL Server stored procedures along with lines of code for each procedure

which is slightly different from yours but related to it.

+3
source

1) Save all your views, triggers, functions, stored procedures, table schemas, etc. in Source Control and use this as a wizard.

2) Otherwise, use the reference database as the main and script views and stored procedures, etc.: right-click DB, Tasks-> Generate Scripts and select your objects.

3) You can even use transactional replication between the reference and target databases.

I strongly believe that the best way is to create an entire script and put it in Source Control.

+6
source

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

So, if you or your organization has an MSDN subscription, you may need to download and install the Database Edition on your Command System to get all the features now.

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

+3
source

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

This is a C # console application that creates scripts of SQL database objects using SMO. You can use this to compare scripts generated on two servers. Starting with its open source, configure it if you need it.

Timur

+3
source

More articles:


All Articles