Find Tables missing metadata columns

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.

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

<#@ 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("-->");
#>

---

Submitted by David Darden.