Import/Export Table SQL Script

The following script generates SQL code to export table definition from one TimeXtender Master Data Management 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 TimeXtender Master Data Management 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