Following script generates SQL code to export table definition from one exMon DM database instance to another.
- Run the script on the source database, copy the output and execute on destination.
- Script is run per table and the variable @ExportTableDBName needs to be set to the database table name at source database.
-- Import/Export exMonDM Script -- Change @ExportTableDBName declare @ExportTableDBName as varchar(120) set @ExportTableDBName='tabledbname' declare @STableDisplayName as varchar(200) declare @STableDatabaseName as varchar(200) declare @SDescription as varchar(800) declare @SSchemaName as varchar(200) declare @SProjectId as int declare @SSectionId as int declare @SUserId as int declare @SUserMachineInfo as varchar(200) declare @SexTablesVersion as varchar(200) declare @ResTableId as int select @STableDisplayName=zz.TableName, @STableDatabaseName=zz.dbname, @SDescription=zz.[description], @SSchemaName=zz.[Schema], @SProjectId=ps.ProjectId, @SSectionId=ps.Id, @SUserId=-1, @SUserMachineInfo='', @SexTablesVersion='' from ( select row_number() over (partition by TableId order by versionid desc) as rnTableId,dbname,tableid,[schema],versionid,[TableName],[ShortName],description from dbo.[table] where dbname like @ExportTableDBName ) zz left join dbo.Section_To_Table stt on objectid=zz.tableid and objecttype='Table' left join dbo.ProjectSection ps on stt.SectionId=ps.Id where zz.rnTableId=1 declare @ProjectName as varchar(200) declare @SectionName as varchar(200) set @ProjectName=(select ProjectName from dbo.[project] where ProjectId=@SProjectId) set @SectionName=(select SectionName from dbo.projectsection where ProjectId=@SProjectId and Id=@SSectionId) print 'Project Name is ' + @ProjectName + ' and Project Id is ' + cast(@SProjectId as varchar) print 'Section Name is ' + @SectionName + ' and Section Id is ' + cast(@SSectionId as varchar) declare @ProjectSQL as varchar(500) set @ProjectSQL='declare @ProjectId as int' + char(13) set @ProjectSQL=@ProjectSQL+'declare @SectionId as int' + char(13) set @ProjectSQL=@ProjectSQL+'set @ProjectId=(select ProjectId from dbo.[project] where ProjectName='''+@ProjectName+''')' + char(13) set @ProjectSQL=@ProjectSQL+'set @SectionId=(select Id from dbo.[projectsection] where SectionName='''+@SectionName+''')' + char(13) print 'Table is '+@STableDatabaseName+'.'+isnull(@SSchemaName,'<noschema>')+'.['+isnull(@STableDisplayName,'<missing>')+']' declare @CreateTableSQL as varchar(220) set @CreateTableSQL = 'declare @ResTableId as int' set @CreateTableSQL = @CreateTableSQL + char(13) + 'exec client.usp_setup_createTable '''+@STableDisplayName+''','''+@STableDatabaseName+''','''+@SDescription+''','+isnull(''''+@SSchemaName+'''','NULL')+',@ProjectId,@SectionId,'+cast(@SUserId as varchar)+','''+@SUserMachineInfo+''','''+@SexTablesVersion+''',@ResTableId=@ResTableId OUTPUT' print '' print 'SQL for exec client.usp_createTable done' declare @NewTableId as int declare @TableVersionId as int select @ResTableId=TableId,@TableVersionId=VersionId from ( select row_number() over (partition by TableId order by VersionId desc) as rn,TableId,VersionId from dbo.[Table] where [dbname]=@ExportTableDBName ) zz where zz.rn=1 IF OBJECT_ID('tempdb..#columns') IS NOT NULL DROP TABLE #columns print '' print 'TableID is ' + cast(@ResTableId as varchar) + ' and Version is ' + cast(@TableVersionId as varchar) select row_number() over (order by columnname desc) as rn, ColumnName, ColumnType, DataType, Size, IsReadOnly, LookupDefinition into #Columns from dbo.[tablecolumn] where TableId=@ResTableId and TableVersionId=@TableVersionId declare @ColCount as int set @ColCount=(select count(*) from #columns) declare @SQLAddcol as varchar(5000) declare @ColumnName as varchar(120) declare @ColumnType as varchar(120) declare @DataType as varchar(120) declare @Size as varchar(10) declare @IsReadOnly as varchar(10) declare @LookupDef as varchar(500) set @SQLAddCol='declare @ResColumnId as int' + char(13) while (@ColCount>0) begin select @ColumnName=ColumnName,@ColumnType=ColumnType,@DataType=DataType,@Size=Size,@IsReadOnly=IsReadOnly,@LookupDef=LookupDefinition from #Columns where rn=@ColCount IF OBJECT_ID('tempdb..#lookupdef') IS NOT NULL DROP TABLE #lookupdef select parsename(replace(x.value,':','.'),2) as keyname,parsename(replace(x.value,':','.'),1) as val into #LookupDef from ( select value from string_split(@LookupDef,';') ) x where nullif(x.value,'') is not null declare @c as int set @c=(select count(*) from #LookupDef) print 'LookupDef has ' + cast(@c as varchar) + ' rows' declare @LUPTableId as varchar(20) declare @LUPKeyId as varchar(20) declare @LUPDisplayId as varchar(20) declare @LUPSortId as varchar(20) if @c>0 begin set @LUPTableId=(select val from #LookupDef where keyname='TableId') set @LUPKeyId=(select val from #LookupDef where keyname='KeyId') set @LUPDisplayId=(select val from #LookupDef where keyname='DisplayId') set @LUPSortId=(select val from #LookupDef where keyname='SortId') end print 'LookupTID:'+cast(@LUPTableId as varchar) print ' - Adding Column ' + @ColumnName set @SQLAddCol = @SQLAddCol + 'exec client.usp_setup_addColumn -1,'''','''',@ResTableId,'''+@ColumnName+''','''+isnull(@ColumnType,'<noct>')+''','''+isnull(@DataType,'NULL')+''','+isnull(cast(@Size as varchar),'NULL')+','+isnull(@IsReadOnly,'NULL')+','+isnull(@LUPTableId,'NULL')+','+isnull(@LUPKeyId,'NULL')+','+isnull(@LUPDisplayId,'NULL')+',@ResColumnId' set @SQLAddCol = @SQLAddCol + char(13) set @ColCount = @ColCount-1 end print 'SQL for exec client.usp_setup_addColumn done' print 'Column count is '+cast(@ColCount as varchar) print '' print 'Generate SQL is done' print '==============================================================' print 'Following is SQL to Generate Table and Columns:' print ' ' print @ProjectSQL print ' ' print @CreateTableSQL print 'print ''ReturnTableId is ''+isnull(cast(@ResTableId as varchar),''<missing>'') ' print ' ' print @SQLAddCol