Import a filtered list of tables using a static list of table names

Scott Currie

A list of tables either from a C# code nugget or a metadata table is used to filter the list of imported table schema information from a live database.

published 01.14.14

last updated 01.14.14

Content
Discussion 5

                            


<!-- Connection.biml -->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection Name="Source" ConnectionString="Provider=SQLNCLI10;Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
    </Connections>
</Biml>
 
<!-- Tables.biml -->
<# var sourceConnection = (AstDbConnectionNode)RootNode.Connections["Source"]; #>
<# var importedSchema = sourceConnection.ImportDB("", "", ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews); #>

<#
// 1. Uncomment the following line for a list retrieved from a database table
// var tableNamesToImport = ExternalDataAccess.GetDataTable(sourceConnection.ConnectionString, "SELECT TableName FROM TableNames").Rows.OfType<System.Data.DataRow>().Select(r => r[0].ToString());

// 2. Uncomment the following line for a static list
// var tableNamesToImport = new List<string>() { "Table1", "Table2", "Table3", "Table4", "Table5", "Table6" };
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <# foreach (var tableNode in importedSchema.TableNodes.Where(item => tableNamesToImport.Contains(item.Name)).OrderBy(item => item.Name)) { #>
            <!-- Do whatever you were planning to do with your now filtered and sorted list of table nodes -->
            <#=tableNode.GetBiml()#>
        <# } #>
    </Tables>
</Biml>

<!-- Use the following T-SQL DDL to create the database table to store table names -->
<!--
CREATE TABLE [dbo].[TableNames](
       [TableName] [nvarchar](256) NOT NULL
) ON [PRIMARY]
-->

While the ImportDB method has a table filter string, it uses the LIKE wildcard syntax, and can be difficult to use when you have multiple disparate search patterns. In this sample, we give two options: either use a statically defined list of table names or access a list of table names from a database table.

For this sample to work, you MUST uncomment the line of code corresponding to the filter type you want to use.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

Jordi

1:27pm 06.20.14

Hi Scott. Thanks for this script, very usefull! But i do have a question

I've got a list of tables that have really familair names and quite a few tables are not selected during the foreach loop. A few examples of tables:

Planobject <-- is not added PlanobjectHistorie <-- is not added Planobjectstatus <-- is not added PlanobjectVerkoop <-- is not added PlanObjectVerkoopStatus <-- is Added!

I guess it has something to do with the where clause on TableNodes? Hope you can help me.

Kind regards

Jordi

Frederik

1:24pm 08.22.14

Hi Thanx for a good script although it's not peformant if you have a database with lots of tables in it.

I solved it this way. Please comment if it's a bad approach! Regards Frederik

<#@ template tier="1" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<#
    var SourceConnection = RootNode.OleDbConnections["NavisionConnection"];
    var TargetConnection = RootNode.OleDbConnections["RawConnection"];
    string metadataCommand =    "SELECT\n"  
                            +   "SchemaName = '[' + ts.[name] + ']',\n"
                            +   "TableName =  t.Name \n"
                            +   "FROM sys.[views] v\n"                  
                            +   "JOIN sys.schemas vs ON vs.[schema_id] = v.[schema_id]\n"
                            +   "JOIN sys.tables t ON t.[name] = v.[name]\n"
                            +   "JOIN sys.schemas ts ON ts.[schema_id] = t.[schema_id]\n"
                            +   "WHERE vs.[name] = 'bi';";

    var tableNamesToImport = ExternalDataAccess.GetDataTable(SourceConnection.ConnectionString, metadataCommand).Rows.OfType<System.Data.DataRow>().Select(r => r[1].ToString());//.Select(r => r[0].ToString());
    var tableNodes = SourceConnection.GenerateTableNodes("dbo",tableNamesToImport);

#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>            
        <#  foreach(var table in tableNodes) { #>
        <#=table.GetBiml() #>
        <# } #>
    </Tables>       
</Biml>

Peter7

4:58pm 09.15.14

And worth noting that table names are case-sensitive when trying to use the List option. That always gets me.

Frank5

8:35am 09.30.16

Hi Scot,

Nice script. Is there a way to get the values for the list from a file in the project using a relative path?

Cheers

Tim03

9:33am 10.17.18

I'm struggling to get this to work with an ODBC connection to a mySQL database hosted on Amazon AWS. I had it working a couple days ago but somehow a change I made somewhere broke it (at least I know it is possible). My current issue is I am consistantly getting 'object reference not set to an instance of an object'. I've pasted what I think is the relevant code below. Can you see anything obviously wrong here?

<# var sourceConnection = (AstDbConnectionNode)RootNode.Connections["Source"]; #> <# var importedSchema = sourceConnection.ImportDB(); #>

Import a filtered list of tables using a static list of table names

Share

Comments Add Comment