Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Last active December 27, 2015 12:29
Show Gist options
  • Save lionofdezert/7326023 to your computer and use it in GitHub Desktop.
Save lionofdezert/7326023 to your computer and use it in GitHub Desktop.
Script to find and create missing tables to synchronize two databases
--DROP IF ALREADY CREATED
IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateMissingTables')
DROP PROC [dbo].[SYNC_CreateMissingTables]
GO
--CREATE PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Stored Procedure, to create missing tables at target database by compareing
both source AND target databases.
EXEC [dbo].[SYNC_CreateMissingTables] 'AdventureWork_StableVersion','AventureWork_Target'
*/
CREATE PROCEDURE [dbo].[SYNC_CreateMissingTables]
(
@SyncFromDB VARCHAR(200), --Source database name
@SyncToDB VARCHAR(200) --Target database name
)
AS
BEGIN
CREATE TABLE #AlterStatements (TSQLStmt VARCHAR(MAX))
EXEC
( 'INSERT INTO #AlterStatements
SELECT '' IF NOT EXISTS (SELECT 1 FROM '+@SyncToDB+'.SYS.TABLES WHERE NAME = '''''' + so.name + '''''')
BEGIN create table '+@SyncToDB+'.dbo.['' + so.name + ''] ('' +
REVERSE(CASE WHEN LEFT(LTRIM(REVERSE(o.list)),1)='',''THEN SUBSTRING(REVERSE(o.list),CHARINDEX('','',
REVERSE(o.list),1)+1,LEN(REVERSE(o.list))) ELSE REVERSE(o.list) END) + '')''
+ CASE WHEN tc.Constraint_Name IS NULL THEN '' END ''
ELSE ''ALTER TABLE '+@SyncToDB+'.dbo.['' + so.Name + ''] ADD CONSTRAINT [''
+ tc.Constraint_Name + ''] PRIMARY KEY '' + '' (''
+ LEFT(j.List, Len(j.List) - 1) + '') END ''
END
FROM ' + @SyncFromDB
+ '.dbo.sysobjects so
CROSS APPLY ( SELECT '' ['' + column_name + ''] '' + data_type
+ case data_type
WHEN ''sql_variant'' THEN ''''
WHEN ''text'' THEN ''''
WHEN ''ntext'' THEN ''''
WHEN ''image'' THEN ''''
WHEN ''decimal''
THEN ''(''
+ CAST(numeric_precision as varchar)
+ '', ''
+ CAST(numeric_scale as varchar)
+ '')''
ELSE coalesce(''(''
+ case WHEN character_maximum_length = -1
THEN ''MAX''
ELSE CAST(character_maximum_length as varchar)
END + '')'', '''')
END + '' ''
+ case WHEN exists ( select id
from '
+ @SyncFromDB
+ '.dbo.syscolumns
WHERE object_name(id) = so.name
AND name = column_name
AND columnproperty(id, name, ''IsIdentity'') = 1 )
THEN ''IDENTITY(''
+ CAST(ident_seed(''[''+so.name+'']'') as varchar)
+ '',''
+ CAST(ident_incr(''[''+so.name+'']'') as varchar)
+ '')''
ELSE ''''
END + '' ''
+ ( case WHEN IS_NULLABLE = ''No'' THEN ''NOT '' ELSE '''' END ) + ''NULL ''
+ case WHEN COLUMN_DEFAULT IS NOT NULL
THEN ''DEFAULT ''
+ COLUMN_DEFAULT
ELSE ''''
END + '', ''
from ' + @SyncFromDB
+ '.information_schema.columns
WHERE table_name = so.name
order by ordinal_position
FOR
XML PATH('''')
) o ( list )
LEFT JOIN ' + @SyncFromDB
+ '.information_schema.table_constraints tc on tc.Table_name = so.Name
AND tc.Constraint_Type = ''PRIMARY KEY''
CROSS APPLY ( select ''['' + Column_Name + ''], ''
FROM ' + @SyncFromDB
+ '.information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR
XML PATH('''')
) j ( list )
WHERE so.xtype = ''U''
AND EXISTS
(
SELECT *
FROM ' + @SyncFromDB + '.Sys.tables MasterDB
LEFT JOIN ' + @SyncToDB
+ '.sys.tables TargetDB ON MasterDB.name=TargetDB.name
WHERE TargetDB.name is null
AND MasterDB.type_desc=''USER_TABLE''
AND MasterDB.NAME=so.NAME AND so.id=MasterDB.[OBJECT_ID]
)
ORDER BY so.name '
)
----CURSOR TO EXECUTE/PRINT EACH STATEMENT SEPARATELY-----
DECLARE MY_CURSOR CURSOR READ_ONLY
FOR
SELECT TSQLStmt FROM #AlterStatements
OPEN My_Cursor
DECLARE @SQLstmt VARCHAR(MAX)
Fetch NEXT FROM MY_Cursor INTO @SQLstmt
While (@@FETCH_STATUS <> -1)
BEGIN
EXEC (@SQLstmt)
-- PRINT (@SQLstmt)
FETCH NEXT FROM MY_CURSOR INTO @SQLstmt
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
-----
DROP TABLE #AlterStatements
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment