Find Tables missing metadata columns

gravatar

David

This is a validation script we created to verify that all tables in a project have our standard metadata columns added to them.

published 04.19.14

last updated 04.19.14


Share

Tags

  • Metadata
  • Script
  • Validation
                            


<#
/*============================================================================= 
Description:     
This script will find any tables that are missing our standard Metadata columns
in the project.
    
===============================================================================
History:
20140418    david.darden    Initial Version 
=============================================================================*/
#>

<#@ template language="C#" #>
<#@ annotation annotationtype="Description" tag="Summary" text="VERIFY REQUIRED.  Validate that all Tables have Metadata columns." #>
<#
WriteLine("<!--");

WriteLine("VALIDATION:  Check tables have all metadata columns");
WriteLine("SUCCESS CRITERIA:\nNo bad table names should be returned.\n");

var isDebug = true;
var goodTables = new List<string>();
var badTables = new List<string>();
var columnList = "EDW_INSERT_TS,EDW_MOD_TS,EDW_SOURCE_ID,EDW_CYCLE_KEY";

WriteLine("Tables missing metadata:");
foreach (AstTableNode table in RootNode.SymbolTable[typeof(AstTableNode)])
{
	var tableName = table.Name;
	List<string> missingColumns = new List<string>();
	
	foreach (var columnName in columnList.Split(new char[] { ',' }))
	{
		if (!table.Columns.Any(item => item.Name == columnName))
		{
			missingColumns.Add(columnName);
		}
	}
	
	if(missingColumns.Count == 0)
	{
		goodTables.Add(tableName);
    }
	else
	{
		badTables.Add(tableName);
		var missingColumnsMessage = string.Join(", ", missingColumns);
		WriteLine("  {0}: {1}", tableName, missingColumnsMessage);
		//ValidationReporter.Report(table, Severity.Error, "The Table '{0}' is missing the following columns: '{1}'.", tableName, missingColumnsMessage);
    }
}

if (badTables.Count == 0)
{
	WriteLine("\nNo bad Tables found! ({0} Tables checked)", badTables.Count + goodTables.Count);
}
else
{
	WriteLine("\n{0}/{1} Tables were missing metadata!", badTables.Count, badTables.Count + goodTables.Count);
}

if(isDebug)
{
	WriteLine("\nGood Tables:");
	foreach(var goodTableNames in goodTables)
	{
		WriteLine("  {0}", goodTableNames);
	}
}


WriteLine("-->");
#>

                        

This script looks at all the tables in a project and checks that each one has the proper metadata columns on it. It reports any failures and optionally all the successes. This is a standard validation pattern we use that is run before builds to identify issues when they are cheaper to fix.

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

Comments

There are no comments yet.