Not long ago, I was working on “SQL Synchronizer” -a tool of my own to help synchronize stored procedures from source server / database to target server / database. Use of this tool will become handy when you want to synchronize different environments currently having different versions of stored procedures.

The idea behind developing such tool has struck my mind while moving my changes from development to staging. Though execution of DDL statements went through fine, execution of stored procedures themselves failed as the dependant objects of these stored procedures where not in sync. So, I started to build a .NET project to help me add, delete, update stored procedures either from source / target databases. I shall post the solution to share with you guys, please bare with me for awhile.

Anyways, having synchronizing stored procedures, I thought of synchronizing tables as well. I couldn’t come up with any ideas, however as a first step I thought of synchronizing table scripts. Normally, SQL scripts are generated using SQL Query Analyzer (right click on table, Script object to New Window as -> Create) and / or Enterprise Manager (right click on table, All tasks, Generate SQL Scripts ), which requires user interaction with these tools.

However, if you want to automate generation of those scripts from the T-SQL and / or stored procedures then you got to use SQL System Tables to pull some information. Having recently exploring the system tables, I have come up with a T-SQL that CREATE TABLE script. This script might get the basic job done, however, if you want sophisticated script generator then please refer to Ryan Randall’s article found here. Please feel free to post your comments and suggestion on this blog entry.

Shyam K. Arjarapu

use pubs;
Go


DECLARE @SQL AS VARCHAR(8000)
DECLARE @SOURCE_TABLE AS VARCHAR(100)
DECLARE @TARGET_TABLE AS VARCHAR(100)

SET @SQL = ''
SET @SOURCE_TABLE = 'authors' 
SET @TARGET_TABLE = 'tmp_authors'

SELECT @SQL = 
	/* get the column name and colum datatype name  */
	@SQL + ' [' + SC.[name] + '] [' +  ST.[name] + '] ' + 
	/* except for the bit data type, get the column size */
	(CASE WHEN ST.[name] <> 'bit' THEN
		'(' + CONVERT( VARCHAR, SC.length) + ')'
	ELSE
		''
	END) + 
	/* except for the user datatype, get the collation  */
	(CASE WHEN ( (SC.collation IS NOT NULL) AND (SC.usertype <> SC.xusertype) ) THEN 
		' COLLATE ' + SC.collation
	ELSE
		''
	END) + 
	/* set if field is null or not null  */
	(CASE WHEN (SC.isnullable = 0) THEN 
		' NOT NULL '
	ELSE
		' NULL '
	END) + ',' + CHAR(13)
  FROM  dbo.[syscolumns] SC JOIN dbo.[systypes] ST
	ON SC.xtype = ST.xtype and sc.xusertype = ST.xusertype
 WHERE  id = (SELECT [id] 
		FROM [dbo].[sysobjects] 
	       WHERE xtype = 'U' AND
		     [name] = @SOURCE_TABLE)
 ORDER  BY colorder

IF @SQL <> ''
BEGIN
	SET @SQL = 'CREATE TABLE dbo.[' +
		@TARGET_TABLE + '] (' + CHAR(13) + 
		LEFT(@SQL, LEN(@SQL) - 2) + ')'

	PRINT @SQL
END