{"id":2660,"date":"2015-12-07T09:58:49","date_gmt":"2015-12-07T09:58:49","guid":{"rendered":"http:\/\/bicortex.com\/?p=2660"},"modified":"2016-10-05T02:22:36","modified_gmt":"2016-10-05T02:22:36","slug":"tableau-server-workgroup-postgresql-database-schema-and-data-synchronization","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/tableau-server-workgroup-postgresql-database-schema-and-data-synchronization\/","title":{"rendered":"Tableau Server Workgroup PostgreSQL Database Schema And Data Synchronization"},"content":{"rendered":"

Last client who engaged me to architect and develop a small data warehouse for them also made a large investment in Tableau as their default reporting platform. The warehouse data, small by today\u2019s standards, was to be uploaded into Tableau server as a scheduled overnight extract and users granted access to reports\/data on Tableau server rather than querying star schema relational tables directly (implemented on the SQL Server database engine). As data availability and therefore meeting BI SLAs was paramount to the project success, a robust notification system was put in place to log any data warehouse issues that may have arisen from all activities on the database server. However, even though all precautions were taken to ensure data warehouse failed processes were logged and corresponding issues mitigated accordingly, Tableau extract failures along with other Tableau server activities were largely unaccounted for due to the lack of data. How could one address this issue?<\/p>\n

Tableau provides access to their internal server PostgreSQL metadata database with just a few simple steps. As it turns out, during installation Tableau Server will create the almost empty ‘workgroup’ repository with over 100+ tables, 900+ columns (about 100 of them used as Keys), 300+ joins and 16+ views which can be accessed and queried. Tableau Server works as a collection processes, processors, programs and applications, like data engine, data server, VizQL Server, application server etc. Each of those processes generates log with data about user activities, data connections, queries and extractions, errors, views and interactions, etc. which is parsed regularly and stored into PostgreSQL-based Tableau Server Administrative Database. PostgreSQL Server containing Workgroup DB usually runs on the same Windows Server as Main Tableau Server or (if Tableau Server runs on multi-mode cluster with Worker Tableau Server(s)) on other Windows Server, which runs Worker Tableau Server and uses non-standard TCP\/IP port 8060. Tableau\u2019s PostgreSQL database access is provided by means of using a few different accounts, each with its own set of privileges. In November 2014 Tableau Software introduced (Release 8.2.5) a new, default user, named ‘readonly’ with read access to all tables and views of Workgroup Repository which is what I\u2019m going to be using to get ‘under the hood’. Other user commonly used for Tableau server metadata exploration, aptly named \u2018tableau\u2019 can also be used for Tableau server activity analysis but has access to fewer database objects.<\/p>\n

The easiest way to connect to Tableau\u2019s ‘workgroup’ database using ‘readonly’ account is opening an administrator command prompt on your Tableau Server, navigating to your Tableau Server bin directory and issuing the tabadmin dbpass command, specifying your chosen password. After server restart the changes should take effect and you should be able to see the following output.<\/p>\n

\"Tableau_Workgroup_DB_Sync_Enable_DB_Access\"<\/a><\/p>\n

PostgreSQL can now be queried using a client\u00a0tool of your choice e.g. PgAdmin. This is what the ‘public’ schema with all its tables looks like when imported in Navicat Data Modeller (click on image to enlarge).<\/p>\n

\"Tableau_Workgroup_DB_Sync_DB_ERD_v9point1\"<\/a><\/p>\n

In order to connect to it from SQL Server we can simply download PostgreSQL ODBC driver and configure it with the credentials of the ‘readonly’ user.<\/p>\n

\"Tableau_Workgroup_DB_Sync_ODBC_Setup\"<\/a><\/p>\n

All there is left to do is to create a linked server connection to PostgreSQL database directly from SQL Server Management Studio, exposing a collection of objects (tables and views) on the public schema.<\/p>\n

\"Tableau_Workgroup_DB_Sync_MSSQL_LinkedSvr_Schema_View\"<\/a><\/p>\n

Now we should be able to OPENQUERY Tableau metadata objects with ease but if we would like to go further and regularly copy Tableau\u2019s data across to SQL Server (in case of my client that was precisely the requirement in order not to interfere with production database), the following code should provide this functionality.<\/p>\n

Firstly, let\u2019s create a sample database called ‘TableauDBCopy’ and a ‘tab’ schema on the SQL Server target instance. The below SQL snippet also creates and populates a small table called ‘tabSchemaObjectsExclude’ on the ‘dbo’ schema which stores table names we don\u2019t want to import. The reason for this exclusion is that these tables do not contain the primary keys, therefore it is impossible to compare the two schemas using the code below which relies on primary key being defined on every single table object.<\/p>\n

\r\n--CREATE 'TableauDBCopy' DATABASE AND 'tab' SCHEMA ON THE TARGET INSTANCE\/SERVER\r\nUSE [master];\r\nGO\r\nIF EXISTS ( SELECT  name\r\n            FROM    sys.databases\r\n            WHERE   name = N'TableauDBCopy' )\r\n    BEGIN\r\n        ALTER DATABASE TableauDBCopy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\n        DROP DATABASE TableauDBCopy;\r\n    END;\r\nGO\r\nCREATE DATABASE TableauDBCopy ON \r\n( NAME = 'TableauDBCopy_dat',\r\n   FILENAME = 'D:\\SQLData\\MSSQL12.ServerName\\MSSQL\\DATA\\TableauDBCopy.mdf',\r\n   SIZE = 500MB,\r\n   MAXSIZE = 2000MB,\r\n   FILEGROWTH = 100 ) LOG ON\r\n( NAME = 'TableauDBCopy_log',\r\n   FILENAME = 'D:\\SQLData\\MSSQL12.ServerName\\MSSQL\\DATA\\TableauDBCopy.ldf',\r\n   SIZE = 100MB,\r\n   MAXSIZE = 1000MB,\r\n   FILEGROWTH = 50MB );\r\nGO\r\nEXEC TableauDBCopy.dbo.sp_changedbowner @loginame = N'SA', @map = false;\r\nGO\r\nALTER DATABASE TableauDBCopy SET RECOVERY SIMPLE;\r\nGO\r\nUSE TableauDBCopy;\r\nGO\r\nCREATE SCHEMA tab AUTHORIZATION dbo;\r\nGO\r\n\r\n--CREATE EXCEPTION 'tabSchemaObjectsExclude' TABLE AND POPULATE IT WITH EXCEPTION DATA\r\nCREATE TABLE dbo.tabSchemaObjectsExclude\r\n(ObjectName VARCHAR (256))\r\nGO\r\nINSERT INTO dbo.tabSchemaObjectsExclude( ObjectName )\r\nSELECT 'dataengine_configurations'\t\t\t\t\t\tUNION ALL\r\nSELECT 'exportable_repository_id_columns'\t\t\t\tUNION ALL\r\nSELECT 'exportable_tables_column_transformations'\t\tUNION ALL\r\nSELECT 'monitoring_dataengine'\t\t\t\t\t\t\tUNION ALL\r\nSELECT 'monitoring_postgresql'\t\t\t\t\t\t\tUNION ALL\r\nSELECT 'permission_reasons'\t\t\t\t\t\t\t\tUNION ALL\r\nSELECT 'schema_migrations'\t\t\t\t\t\t\t\tUNION ALL\r\nSELECT 'users_view'\r\n<\/pre>\n
Next, let\u2019s look at a simple stored procedure which compares the source schema (Tableau server public schema on the PostgreSQL database) with the target schema (our newly created SQL Server database with the ‘tab’ schema). This code is used to interrogate both databases for their ‘compatibility’ and tables metadata structure e.g. data types, character lengths, NULL-ablity, precision, scale etc. and if the target object(s) are found to be missing or out of sync with the source version, it creates a DROP and a CREATE table SQL DDLs statement on the fly and applies the changes directly in the target environment.<\/p>\n
\r\nUSE [TableauDBCopy]\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE [dbo].[usp_checkRemoteTableauServerTablesSchemaChanges]\r\n    (\r\n\t  @Remote_Server_Name\t\t\t\tVARCHAR\t\t\t(256),\r\n\t  @Remote_Server_DB_Name\t\t\tVARCHAR\t\t\t(128),\r\n\t  @Remote_Server_DB_Schema_Name\t\tVARCHAR\t\t\t(128),\r\n\t  @Target_DB_Name\t\t\t\t\tVARCHAR\t\t\t(128),\r\n\t  @Target_DB_Schema_Name\t\t\tVARCHAR\t\t\t(128),\r\n      @Is_All_OK\t\t\t\t\t\tINT\t\t\t\t\t\t\tOUTPUT ,\r\n      @Process_Name\t\t\t\t\t\tVARCHAR\t\t\t(250)\t\tOUTPUT ,\r\n      @Error_Message\t\t\t\t\tVARCHAR\t\t\t(MAX)\t\tOUTPUT\r\n    )\r\n    WITH RECOMPILE\r\nAS\r\n    SET NOCOUNT ON\r\n    BEGIN\r\n        DECLARE @Is_ReCheck BIT = 0\r\n\t\tDECLARE @SQL NVARCHAR (MAX)\r\n        DECLARE @Is_Debug_Mode BIT = 1\r\n\t\tDECLARE @Remote_Server_Tableau\tVARCHAR(55)\t\t= 'TABPOSTGRESQLPROD'\r\n        SET @Process_Name = ( SELECT    OBJECT_NAME(objectid)\r\n                              FROM      sys.dm_exec_requests r\r\n                                        CROSS   APPLY sys.dm_exec_sql_text(r.sql_handle) a\r\n                              WHERE     session_id = @@spid\r\n                            )\r\n\t\t\r\n\t\tIF OBJECT_ID('tempdb..#t_seqfloats') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #t_seqfloats\t\t\t\r\n            END\r\n\r\n\t\t;WITH    Nbrs_3 ( n )\r\n          AS ( SELECT   1\r\n               UNION\r\n               SELECT   0\r\n             ),\r\n        Nbrs_2 ( n )\r\n          AS ( SELECT   1\r\n               FROM     Nbrs_3 n1\r\n                        CROSS JOIN Nbrs_3 n2\r\n             ),\r\n        Nbrs_1 ( n )\r\n          AS ( SELECT   1\r\n               FROM     Nbrs_2 n1\r\n                        CROSS JOIN Nbrs_2 n2\r\n             ),\r\n        Nbrs_0 ( n )\r\n          AS ( SELECT   1\r\n               FROM     Nbrs_1 n1\r\n                        CROSS JOIN Nbrs_1 n2\r\n             ),\r\n        Nbrs ( n )\r\n          AS ( SELECT   1\r\n               FROM     Nbrs_0 n1\r\n                        CROSS JOIN Nbrs_0 n2\r\n             )\r\n\r\n\t\t\t SELECT    'float' + CAST(n AS VARCHAR(2)) seq_floats \r\n\t\t\t INTO #t_seqfloats\r\n                              FROM      ( SELECT    ROW_NUMBER() OVER ( ORDER BY n )\r\n                                          FROM      Nbrs\r\n                                        ) D ( n )\r\n                              WHERE     n &lt;= 53\r\n\t\t\tUNION ALL \r\n\t\t\tSELECT 'float'\r\n\r\n        Check_RemoteSvr_Schema:\r\n        IF OBJECT_ID('tempdb..#t_allTblMetadata') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_allTblMetadata]\t\t\t\r\n            END  \r\n\t\tCREATE TABLE tempdb..[#t_allTblMetadata]\r\n\t\t(\r\n\t\ttable_name VARCHAR(256),\r\n\t\tcolumn_name VARCHAR(256),\r\n\t\tordinal_position INT ,\r\n\t\tis_nullable BIT ,\r\n\t\tdata_type VARCHAR (256) ,\r\n\t\tcharacter_maximum_length BIGINT,\r\n\t\tnumeric_scale SMALLINT,\r\n\t\tnumeric_precision SMALLINT,\r\n\t\tis_primary_key BIT,\r\n\t\tlocal_schema_name VARCHAR(55),\r\n\t\tremote_schema_name VARCHAR(55),\r\n\t\tlocal_or_remote VARCHAR(25) \r\n\t\t)\r\n\t  \r\n        SET @SQL = '\r\n\t\t\tINSERT INTO #t_allTblMetadata\r\n\t\t\t\t(\r\n\t\t\t\t\t[table_name]\r\n\t\t\t\t\t,[column_name]\r\n\t\t\t\t\t,[ordinal_position]\r\n\t\t\t\t\t,[is_nullable]\r\n\t\t\t\t\t,[data_type]\r\n\t\t\t\t\t,[character_maximum_length]\r\n\t\t\t\t\t,[numeric_scale]\r\n\t\t\t\t\t,[numeric_precision]\r\n\t\t\t\t\t,[is_primary_key]\r\n\t\t\t\t\t,[local_schema_name]\r\n\t\t\t\t\t,[remote_schema_name]\r\n\t\t\t\t\t,[local_or_remote]\r\n\t\t\t\t)\r\n\t\t\tSELECT \r\n\t\t\tLTRIM(RTRIM(a.table_name))\t\t\t\t\t\tAS table_name,\r\n\t\t\tLTRIM(RTRIM(a.column_name))\t\t\t\t\t\tAS column_name, \r\n\t\t\tLTRIM(RTRIM(a.ordinal_position))\t\t\t\tAS ordinal_position,\r\n\t\t\tCASE WHEN a.is_nullable = ''YES''\r\n\t\t\tTHEN 1 ELSE 0 END \t\t\t\t\t\t\t\tAS is_nullable,\r\n\t\t\tLTRIM(RTRIM(a.udt_name))\t\t\t\t\t\tAS data_type,\r\n\t\t\t--a.data_type AS data_type,\r\n\t\t\tLTRIM(RTRIM(a.character_maximum_length))\t\tAS character_maximum_length,\r\n\t\t\tLTRIM(RTRIM(a.numeric_scale))\t\t\t\t\tAS numeric_scale,\r\n\t\t\tLTRIM(RTRIM(a.numeric_precision))\t\t\t\tAS numeric_precision,\r\n\t\t\t--b.primary_key_definition,\r\n\t\t\tCASE WHEN b.PK_column_name IS NULL \r\n\t\t\tTHEN 0 ELSE 1 END\t\t\t\t\t\t\t\tAS is_primary_key,\r\n\t\t\t''tab''\t\t\t\t\t\t\t\t\t\t\tAS local_schema_name,\r\n\t\t\tLTRIM(RTRIM(a.table_schema))\t\t\t\t\tAS remote_schema_name,\r\n\t\t\t''remote''\t\t\t\t\t\t\t\t\t\tAS local_or_remote\r\n\t\t\tFROM OPENQUERY(' +@remote_server_name+ ', \r\n\t\t\t''select  \r\n\t\t\tc.table_name, \r\n\t\t\tc.column_name, \r\n\t\t\tc.ordinal_position, \r\n\t\t\tc.is_nullable, \r\n\t\t\tc.data_type, \r\n\t\t\tc.udt_name ,\r\n\t\t\tc.character_maximum_length, \r\n\t\t\tc.numeric_scale, \r\n\t\t\tc.numeric_precision, \r\n\t\t\tc.table_schema\r\n\t\t\tfrom information_schema.columns c \t\r\n\t\t\twhere c.table_catalog = ''''workgroup'''' and c.table_schema = ''''public'''''') a\r\n\t\t\tLEFT JOIN \r\n\t\t\tOPENQUERY(' +@remote_server_name+ ', \r\n\t\t\t''select \r\n\t\t\tcl.relname as table_name, \r\n\t\t\tco.conname as constraint_name, \r\n\t\t\tco.contype conatraint_type,\r\n\t\t\tpg_get_constraintdef(co.oid) AS primary_key_definition ,\r\n\t\t\tns.nspname as schema_name,\r\n\t\t\tpa.attname as PK_column_name\r\n\t\t\tfrom pg_class cl join pg_constraint co on cl.oid = co.conrelid \r\n\t\t\tjoin pg_namespace ns on cl.relnamespace = ns.oid\r\n\t\t\tjoin pg_attribute pa on pa.attrelid = cl.oid and pa.attnum = co.conkey[1] \r\n\t\t\twhere co.contype = ''''p''''\r\n\t\t\tand cl.relkind=''''r''''\r\n\t\t\tand ns.nspname = ''''public'''''') b \r\n\t\t\tON a.table_name = b.table_name AND a.table_schema = b.[schema_name] AND a.column_name = b.PK_column_name\t\t\t\r\n\t\t\tWHERE SUBSTRING(a.table_name, 1, 1) &lt;&gt; ''_'' AND SUBSTRING(a.table_name, 1, 7) &lt;&gt; ''orphans''\r\n\t\t\tAND NOT EXISTS (SELECT objectname FROM TableauDBCopy.dbo.tabSchemaObjectsExclude o WHERE o.objectname = a.table_name)\t\t\t\r\n\t\t\tORDER BY a.table_name, a.ordinal_position'\r\n\t\t\r\n\r\n\t\tIF @Is_Debug_Mode = 1\r\n\t\tBEGIN\r\n\t\t\tPRINT CHAR(13) + 'SQL statement for acquiring ''source'' tables metadata into #t_allTblMetadata temp table:'\r\n\t\t\tPRINT '-----------------------------------------------------------------------------------------------'\r\n\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\tEND\r\n\r\n\t\tEXEC(@SQL)\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tSELECT  '#t_allTblMetadata table content for remote objects metadata:' AS 'HINT'\r\n                SELECT  *\r\n                FROM    #t_allTblMetadata WHERE local_or_remote = 'Remote'\r\n\t\t\t\tORDER BY table_name, ordinal_position\r\n\t\t\tEND\r\n\t\t\r\n        IF @Is_ReCheck = 1\r\n            BEGIN\r\n                GOTO Check_Local_Schema\r\n            END\r\n\r\n        Check_Local_Schema:  \r\n\r\n\r\n\t\tSET @SQL = \r\n\t\t'INSERT INTO #t_allTblMetadata\r\n\t\t\t\t(\r\n\t\t\t\t\t[table_name]\r\n\t\t\t\t\t,[column_name]\r\n\t\t\t\t\t,[ordinal_position]\r\n\t\t\t\t\t,[is_nullable]\r\n\t\t\t\t\t,[data_type]\r\n\t\t\t\t\t,[character_maximum_length]\r\n\t\t\t\t\t,[numeric_scale]\r\n\t\t\t\t\t,[numeric_precision]\r\n\t\t\t\t\t,[is_primary_key]\r\n\t\t\t\t\t,[local_schema_name]\r\n\t\t\t\t\t,[remote_schema_name]\r\n\t\t\t\t\t,[local_or_remote]\r\n\t\t\t\t)\t\t\r\n        SELECT  \r\n\t\t\t\tt.name AS table_name ,\r\n\t\t\t\tc.name AS column_name ,\r\n\t\t\t\tc.column_id AS ordinal_position ,\r\n\t\t\t\tc.is_nullable ,\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\ttp.name AS data_type ,\r\n\t\t\t\tc.max_length AS character_maximum_length ,\r\n\t\t\t\tc.scale AS numeric_scale ,\r\n\t\t\t\tc.precision AS numeric_precision ,\r\n\t\t\t\tISNULL(idx.pk_flag,0) as ''is_primary_key'' ,\r\n\t\t\t\tss.name ,\r\n\t\t\t\t''public'' ,\r\n\t\t\t\t''local'' AS local_or_remote\r\n\t\tFROM    sys.tables t\r\n\t\tJOIN sys.columns c ON t.object_id = c.object_id\r\n\t\tJOIN sys.types tp ON c.user_type_id = tp.user_type_id\r\n\t\tJOIN sys.objects so ON so.object_id = t.object_id\r\n\t\tJOIN sys.schemas ss ON so.schema_id = ss.schema_id\r\n\t\tLEFT JOIN\t\t(select i.name as index_name, i.is_primary_key as pk_flag, OBJECT_NAME(ic.OBJECT_ID) AS table_name,\r\n\t\tCOL_NAME(ic.OBJECT_ID,ic.column_id) AS column_name FROM sys.indexes AS i INNER JOIN \r\n\t\tsys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n\t\tAND i.index_id = ic.index_id\r\n\t\tWHERE   i.is_primary_key = 1) idx on idx.table_name = t.name and idx.column_name = c.name\r\n\t\tJOIN INFORMATION_SCHEMA.TABLES tt on tt.table_schema = ss.name and tt.table_name = t.name\r\n        WHERE   t.type = ''u'' \t\r\n\t\tAND tt.TABLE_CATALOG = \t'''+@Target_DB_Name+'''\t\t \r\n\t\tAND ss.name = '''+@Target_DB_Schema_Name+''''\r\n\r\n\t\tIF @Is_Debug_Mode = 1\r\n\t\tBEGIN\r\n\t\t\tPRINT 'SQL statement for acquiring ''target'' tables metadata into #t_allTblMetadata temp table:'\r\n\t\t\tPRINT '-----------------------------------------------------------------------------------------------'\r\n\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\tEND\r\n\r\n\t\tEXEC(@SQL)\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tSELECT  '#t_allTblMetadata table content for local objects metadata:' AS 'HINT'\r\n                SELECT  *\r\n                FROM    #t_allTblMetadata WHERE local_or_remote = 'local'\r\n\t\t\t\tORDER BY table_name, ordinal_position\r\n\t\t\tEND\r\n\r\n\r\n        IF OBJECT_ID('tempdb..#t_sql') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_sql]\r\n            END\r\n\r\n        SELECT  DISTINCT\r\n                t1.table_name AS Table_Name ,\r\n                t1.local_schema_name AS Local_Schema_Name ,\r\n                'create table [' + t1.local_schema_name + '].['\r\n                + LOWER(t1.table_name) + '] (' + STUFF(o.list, LEN(o.list), 1, '')\r\n                + ')' + CASE WHEN t2.is_primary_key = 0 THEN ''\r\n                             ELSE '; ALTER TABLE   [' + t1.local_schema_name\r\n                                  + '].[' + t1.table_name + '] '\r\n                                  + ' ADD CONSTRAINT pk_'\r\n                                  + LOWER(t1.local_schema_name) + '_'\r\n                                  + LOWER(t2.table_name) + '_' +  \r\n\t\t\t\t\t\t\t\t  LOWER(REPLACE(t2.pk_column_names,',','_'))\r\n                                  +' PRIMARY KEY CLUSTERED ' + '('\r\n                                  + LOWER(t2.pk_column_names) + ')'\r\n                        END AS Create_Table_Schema_Definition_SQL ,\r\n                'if object_id (''[' + t1.local_schema_name + '].['\r\n                + t1.table_name + ']' + ''', ''U'') IS NOT NULL drop table ['\r\n                + t1.local_schema_name + '].[' + t1.table_name + ']' AS Drop_Table_SQL\r\n        INTO    #t_sql\r\n        FROM    #t_allTblMetadata t1\r\n                CROSS APPLY ( SELECT    '[' + column_name + '] '\r\n                                        +\tCASE\tWHEN data_type IN ( 'bigint',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int8',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'bigserial')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'bigint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN (\t'integer',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial4', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int4',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'oid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'int'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN ( 'smallint', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial2', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'smallserial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int2')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'smallint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN ( 'uuid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'uniqueidentifier'\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN (\t'bool', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'boolean' )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'bit'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN (\t'timestamp', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'timestamptz')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'datetime'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN ( 'bytea',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'json',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'text',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'varchar')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'nvarchar'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type IN ( SELECT * FROM #t_seqfloats )\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t'float'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE data_type \r\n\t\t\t\t\t\t\t\t\t\t\t\t\tEND\t\t\t\t\t\t\t\t\t\r\n                                        +\tCASE\tWHEN data_type\tIN ('int2',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int4',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int8',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'oid',\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'timestamp',\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'uuid',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'bool')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN ''\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN \r\n\t\t\t\t\t\t\t\t\t\t\t\t\tdata_type IN ('text', 'json', 'bytea') OR (data_type = 'varchar' and character_maximum_length IS NULL) OR character_maximum_length &gt; 8000\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN '(max)'\t\t\t \t\t\t\t\t\t\t  \r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type = 'decimal'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN '('\r\n                                                    + CAST(numeric_precision AS VARCHAR)\r\n                                                    + ', '\r\n                                                    + CAST(numeric_scale AS VARCHAR)\r\n                                                    + ')'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN data_type in (SELECT * FROM #t_seqfloats)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN '(53)'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tELSE COALESCE('(' + CAST(character_maximum_length AS VARCHAR) + ')', '')\r\n                                          END + ' '\r\n                                        +( CASE WHEN is_nullable = 0\r\n                                                THEN 'NOT '\r\n                                                ELSE ''\r\n                                           END ) + 'NULL' + ','\r\n                              FROM      #t_allTblMetadata\r\n                              WHERE     table_name = t1.table_name AND local_or_remote = 'Remote'\r\n                              ORDER BY  ordinal_position\r\n                            FOR\r\n                              XML PATH('')\r\n                            ) o ( list )\r\n                JOIN ( SELECT   table_name ,\r\n                                is_primary_key ,\r\n                                pk_column_names ,\r\n                                column_name = REVERSE(RIGHT(REVERSE(pk_column_names),\r\n                                                            LEN(pk_column_names)\r\n                                                            - CHARINDEX(',',\r\n                                                              REVERSE(pk_column_names))))\r\n                       FROM     ( SELECT    table_name ,\r\n                                            is_primary_key ,\r\n                                            pk_column_names = STUFF(( SELECT\r\n                                                              ','\r\n                                                              +CAST(column_name AS VARCHAR(500))\r\n                                                              FROM\r\n                                                              #t_allTblMetadata z2\r\n                                                              WHERE\r\n                                                              z1.table_name = z2.table_name\r\n                                                              AND z2.is_primary_key = 1\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  AND z2.local_or_remote = 'Remote'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  ORDER BY z2.column_name ASC\r\n                                                              FOR\r\n                                                              XML\r\n                                                              PATH('')\r\n                                                              ), 1, 1, '')\r\n                                  FROM      #t_allTblMetadata z1\r\n                                  WHERE     z1.is_primary_key = 1\r\n\t\t\t\t\t\t\t\t\t\t\tAND z1.local_or_remote = 'Remote'\r\n                                  GROUP BY  z1.table_name ,\r\n                                            z1.is_primary_key\r\n                                ) a\r\n                     ) t2 ON t1.table_name = t2.table_name\r\n        WHERE   t1.local_schema_name &lt;&gt; 'unknown' and t1.local_or_remote = 'Remote'\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                SELECT  '#t_sql table content:' AS 'HINT'\r\n               SELECT  *\r\n                FROM    #t_sql\r\n\t\t\t\tORDER BY Table_Name \r\n            END\r\n\r\n        IF @Is_ReCheck = 1\r\n            BEGIN\r\n                GOTO Do_Table_Diff\r\n            END\r\n\r\n\r\n\r\n        Do_Table_Diff:\r\n\r\n\r\n        IF OBJECT_ID('tempdb..#t_diff') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_diff]\r\n            END\r\n\r\n\t\t\t  \r\n        ;WITH    Temp_CTE ( table_name, column_name, is_nullable, data_type, local_schema_name, is_primary_key, character_maximum_length, numeric_scale, numeric_precision )\r\n                  AS (\t\t\t  \t\t\t\t  \t\t\t\t \t\t\t\t \r\n                       SELECT\ttable_name\t\t\t\t\t= m.table_name ,\r\n\t\t\t\t\t\t\t\tcolumn_name\t\t\t\t\t= m.column_name ,\r\n                                is_nullable\t\t\t\t\t= m.is_nullable ,\r\n                                data_type\t\t\t\t\t= CASE\tWHEN m.data_type IN (\t'bigint',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int8',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'bigserial')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'bigint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'integer',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial4', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int4',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'oid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'int'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'smallint', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial2', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'smallserial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int2')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'smallint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'uuid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'uniqueidentifier'\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'bool', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'boolean' )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'bit'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'timestamp', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'timestamptz')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'datetime'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'bytea',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'json',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'text',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'varchar')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'nvarchar'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN ( SELECT * FROM #t_seqfloats )\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'float'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE m.data_type \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tEND,\r\n                                local_schema_name\t\t\t= m.local_schema_name ,\r\n                                is_primary_key\t\t\t\t= m.is_primary_key ,\r\n                                character_maximum_length\t= COALESCE(CASE WHEN \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tm.data_type IN ('text', 'json', 'bytea') OR (m.data_type = 'varchar' and m.character_maximum_length IS NULL) OR m.character_maximum_length &gt; 8000\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN 'max' \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE CAST(m.character_maximum_length AS VARCHAR) END,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tconstants.character_maximum_length ,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCAST(l.character_maximum_length AS VARCHAR)),\r\n                                numeric_scale\t\t\t\t= COALESCE(\tconstants.numeric_scale, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCAST(m.numeric_scale AS VARCHAR),\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCAST(l.numeric_scale AS VARCHAR)),\r\n\t\t\t\t\t\t\t\tnumeric_precision\t\t\t= COALESCE(\tconstants.numeric_precision, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCAST(m.numeric_precision AS VARCHAR), \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tCAST(l.numeric_precision AS VARCHAR))\r\n                       FROM     #t_allTblMetadata m\r\n                                LEFT JOIN ( SELECT  'char' AS data_type ,\r\n                                                    NULL AS character_maximum_length ,\r\n                                                    0 AS numeric_scale ,\r\n                                                    0 AS numeric_precision\r\n                                            UNION ALL\r\n                                            SELECT  'varchar' ,\r\n                                                    NULL ,\r\n                                                    '0' ,\r\n                                                    '0'\r\n\t\t\t\t\t\t\t\t\t\t\tUNION ALL\r\n                                            SELECT  'time' ,\r\n                                                    '5' ,\r\n                                                    '7' ,\r\n                                                    '16'\r\n                                            UNION ALL\r\n                                            SELECT  'date' ,\r\n                                                    '3' ,\r\n                                                    '0' ,\r\n                                                    '10'\r\n                                            UNION ALL\r\n                                            SELECT  'datetime' ,\r\n                                                    '8' ,\r\n                                                    '3' ,\r\n                                                    '23'\r\n                                            UNION ALL\r\n                                            SELECT  'datetime2' ,\r\n                                                    '8' ,\r\n                                                    '7' ,\r\n                                                    '27'\r\n                                            UNION ALL\r\n                                            SELECT  'smalldatetime' ,\r\n                                                    '4' ,\r\n                                                    '0' ,\r\n                                                    '16'\r\n                                            UNION ALL\r\n                                            SELECT  'bit' ,\r\n                                                    '1' ,\r\n                                                    '0' ,\r\n                                                    '1'\r\n                                            UNION ALL\r\n                                            SELECT  'float' ,\r\n                                                    '8' ,\r\n                                                    '0' ,\r\n                                                    '53'\r\n                                            UNION ALL\r\n                                            SELECT  'money' ,\r\n                                                    '8' ,\r\n                                                    '4' ,\r\n                                                    '19'\r\n                                            UNION ALL\r\n                                            SELECT  'smallmoney' ,\r\n                                                    '4' ,\r\n                                                    '4' ,\r\n                                                    '10'\r\n                                            UNION ALL\r\n                                            SELECT  'uniqueidentifier' ,\r\n                                                    '16' ,\r\n                                                    '0' ,\r\n                                                    '0'\r\n                                            UNION ALL\r\n                                            SELECT  'xml' ,\r\n                                                    'max' ,\r\n                                                    '0' ,\r\n                                                    '0'\r\n                                            UNION ALL\r\n                                            SELECT  'numeric' ,\r\n                                                    '9' ,\r\n                                                    '0' ,\r\n                                                    '18'\r\n                                            UNION ALL\r\n                                            SELECT  'real' ,\r\n                                                    '4' ,\r\n                                                    '0' ,\r\n                                                    '24'\r\n                                            UNION ALL\r\n                                            SELECT  'tinyint' ,\r\n                                                    '1' ,\r\n                                                    '0' ,\r\n                                                    '3'\r\n                                            UNION ALL\r\n                                            SELECT  'smallint' ,\r\n                                                    '2' ,\r\n                                                    '0' ,\r\n                                                    '5'\r\n                                            UNION ALL\r\n                                            SELECT  'int' ,\r\n                                                    '4' ,\r\n                                                    '0' ,\r\n                                                    '10'\r\n                                            UNION ALL\r\n                                            SELECT  'bigint' ,\r\n                                                    '8' ,\r\n                                                    '0' ,\r\n                                                    '19'\r\n                                          ) constants ON\t(CASE\tWHEN m.data_type IN (\t\t'bigint',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int8',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'bigserial')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'bigint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t\t'integer',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial4', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int4',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'oid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'int'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t\t'smallint', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial2', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'smallserial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int2')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'smallint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t\t'uuid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'uniqueidentifier'\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t\t'bool', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'boolean' )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'bit'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t\t'timestamp', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'timestamptz')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'datetime'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t\t'bytea',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'json',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'text',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'varchar')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'nvarchar'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN ( SELECT * FROM #t_seqfloats )\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t\t'float'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE m.data_type \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tEND\t) = constants.data_type\r\n                                LEFT JOIN #t_allTblMetadata l ON l.column_name = m.column_name\r\n                                                        AND l.table_name = m.table_name\r\n                                                        AND l.data_type = ( CASE\tWHEN m.data_type IN (\t'bigint',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int8',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'bigserial')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'bigint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'integer',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial4', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int4',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'oid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'int'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'smallint', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'serial2', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'smallserial',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'int2')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'smallint'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'uuid')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'uniqueidentifier'\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'bool', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'boolean' )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'bit'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'timestamp', \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'timestamptz')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'datetime'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN (\t'bytea',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'json',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'text',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t'varchar')\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'nvarchar'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN m.data_type IN ( SELECT * FROM #t_seqfloats )\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tTHEN\t\t\t\t\t'float'\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE m.data_type \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tEND ) AND l.local_or_remote = 'Local'\r\n\t\t\tWHERE m.local_or_remote = 'Remote' \t\t\r\n\t\t\tEXCEPT\r\n\t\t\tSELECT\t\t\t\ttable_name ,\r\n                                column_name ,\r\n                                is_nullable ,\r\n                                data_type , \r\n                                local_schema_name ,\r\n                                is_primary_key ,\r\n                                CASE\tWHEN character_maximum_length &gt; 8000 OR character_maximum_length = -1\r\n\t\t\t\t\t\t\t\tTHEN 'max'\r\n\t\t\t\t\t\t\t\tWHEN data_type IN ('nvarchar', 'nchar') THEN CAST(character_maximum_length\/2 AS VARCHAR)\r\n\t\t\t\t\t\t\t\tELSE CAST(character_maximum_length AS VARCHAR) END AS character_maximum_length,\r\n                                numeric_scale ,\r\n                                numeric_precision\r\n\t\t\tFROM     #t_allTblMetadata \r\n\t\t\tWHERE local_or_remote  ='Local' \r\n                     )\r\n            SELECT DISTINCT\r\n                    table_name ,\r\n                    local_schema_name\r\n            INTO    #t_diff\r\n            FROM    Temp_CTE\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                SELECT  '#t_diff table content:' AS 'HINT'\r\n                SELECT  *\r\n                FROM    #t_diff\r\n            END\r\n\r\n        IF @Is_ReCheck = 1\r\n            GOTO Results\r\n\r\n        Run_SQL:\r\n        IF NOT EXISTS ( SELECT DISTINCT\r\n                                Table_Name ,\r\n                                Local_Schema_Name\r\n                        FROM    #t_sql a\r\n                        WHERE   EXISTS ( SELECT table_name\r\n                                         FROM   #t_diff i\r\n                                         WHERE  a.Table_Name = i.table_name ) )\r\n            BEGIN\r\n                GOTO Schema_Diff_ReCheck\r\n            END\r\n        ELSE\r\n            BEGIN\r\n                DECLARE @schema_name VARCHAR(50)\r\n                DECLARE @table_name VARCHAR(256)\r\n                DECLARE @sql_select_dropcreate NVARCHAR(MAX)\t\t\t\r\n\r\n                DECLARE db_cursor CURSOR FORWARD_ONLY\r\n                FOR\r\n                    SELECT DISTINCT\r\n                            Table_Name ,\r\n                            Local_Schema_Name\r\n                    FROM    #t_sql a\r\n                    WHERE   EXISTS ( SELECT table_name\r\n                                     FROM   #t_diff i\r\n                                     WHERE  a.Table_Name = i.table_name )\r\n                OPEN db_cursor\r\n                FETCH NEXT\r\n\t\t\tFROM db_cursor INTO @table_name, @schema_name\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\r\n                        BEGIN TRY\r\n                            BEGIN TRANSACTION\r\n                            SET @sql_select_dropcreate = ( SELECT\r\n                                                              Drop_Table_SQL\r\n                                                           FROM\r\n                                                              #t_sql \r\n                                                           WHERE\r\n                                                              Table_Name = @table_name\r\n                                                         ) + '; ' +CHAR(13)\r\n                                + ( SELECT  Create_Table_Schema_Definition_SQL \r\n                                    FROM    #t_sql\r\n                                    WHERE   Table_Name = @table_name\r\n                                  ) + REPLICATE(CHAR(13),2)    \r\n\t\t\t\t\t\t\t\t  \r\n\t\t\t\t\t\t\tIF @Is_Debug_Mode = 1\r\n\t\t\t\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\t\t\t\tPRINT 'SQL statement for dropping\/recreating ''source'' table(s):'\r\n\t\t\t\t\t\t\t\t\tPRINT '-----------------------------------------------------------------------------------------------'\r\n\t\t\t\t\t\t\t\t\tPRINT @sql_select_dropcreate\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\tEND\r\n\r\n                            EXEC sp_sqlexec @sql_select_dropcreate\r\n                            --SET @Is_All_OK = 1\r\n                            SET @Error_Message = 'All Good!'\r\n                            COMMIT TRANSACTION\r\n                        END TRY\r\n                        BEGIN CATCH\t\t\r\n                            IF @@TRANCOUNT &gt; 0\r\n                                ROLLBACK TRANSACTION;\r\n                            SET @Is_All_OK = 0\r\n                            SET @Error_Message = 'This operation has been unexpectandly terminated due to error: '''\r\n                                + ERROR_MESSAGE() + ''' at line '\r\n                                + CAST(ERROR_LINE() AS VARCHAR);\t\t\t\t\t\t\t\t\r\n                        END CATCH\r\n                        FETCH NEXT FROM db_cursor INTO @table_name,@schema_name\r\n                    END\r\n                CLOSE db_cursor\r\n                DEALLOCATE db_cursor\r\n                SET @Is_ReCheck = 1\r\n            END\r\n\r\n\r\n        Schema_Diff_ReCheck:\r\n        IF @Is_ReCheck = 1\r\n            BEGIN\r\n                GOTO Check_RemoteSvr_Schema\r\n            END\r\n\r\n        Results:\r\n        IF EXISTS ( SELECT TOP 1\r\n                            *\r\n                    FROM    #t_diff )\r\n            BEGIN \r\n                SET @Is_All_OK = 0\r\n                SET @Error_Message = 'Table schema reconciliation between '\r\n                    + '' + @@SERVERNAME + ''\r\n                    + ' and remote database on '''+@remote_server_name+'''' + CHAR(10)\r\n                SET @Error_Message = @Error_Message + 'failed. Please troubleshoot.'\r\n            END\r\n        ELSE\r\n            BEGIN\r\n                SET @Is_All_OK = 1\r\n                SET @Error_Message = 'All Good!'\r\n            END\r\n\r\n\r\n\t\tIF OBJECT_ID('tempdb..#t_seqfloats') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #t_seqfloats\t\t\r\n            END\r\n        IF OBJECT_ID('tempdb..#t_allTblMetadata') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_allTblMetadata]\r\n            END \r\n        IF OBJECT_ID('tempdb..#t_sql') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_sql]\r\n            END\r\n        IF OBJECT_ID('tempdb..#t_sql') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_diff]\r\n            END\t\r\n    END\r\n<\/pre>\n
Finally, we are ready to load the Tableau PostgreSQL data into the ‘TableauDBCopy’ database tables on the ‘tab’ schema. For that we can use SQL Server Integration Services but since the ‘workgroup’ database is quite small in size and most tables have a primary key defined on them, we can load the data in a sequential order i.e. table by table using a modified version of my database replication stored procedure which I described in one of my previous blog posts HERE<\/a>. The stored procedure works in a similar fashion to the one described previously but allowances needed to be made in order to enable PostgreSQL and SQL Server data types and certain conventions conformance e.g. certain PostgreSQL reserved words need to be encapsulated in double quotes in the OPENQUERY statements in order to be validated and recognized by SQL Server. Likewise, certain SQL Server reserved words need to be used with square brackets delimiters. To reference those exceptions, I have created two views (downloadable from HERE<\/a>) which are used in the merging stored procedure to provide greater cross-database compatibility. The code to the stored procedure works on a table-to-table basis but it it\u2019s would not very hard to make it loop through a collection of objects e.g. using a cursor or ‘Foreach Loop’ SSIS transformation to automate a comprehensive data load. If, on the other hand, a much faster, asynchronous load is required you can always check out one of my previous blog posts on parallel SQL statements execution using SQL Server Agent jobs HERE<\/a>. All the code for the data synchronization across Tableau\u2019s PostgreSQL database and SQL Server instance as well as other T-SQL snippets presented in the post can be downloaded from my OneDrive folder HERE<\/a>.<\/p>\n
Below is a short video depicting how this solution works using both \u2013 schema synchronisation and data synchronisation stored procedures.<\/p>\n