.svg)
Created by: Kristinn Magnusson
The 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
Kristinn is the author of this solution article.
