SqlSchemaModeler

ColumnEntity.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace System.Data.SqlClient
{
    public class ColumnEntity
    {
        private static string[] stringTypes = new[] { "char", "nchar", "nvarchar", "varchar", "varbinary", "uniqueidentifier" };

        public string SqlColumnName;
        public string ColumnName;
        public string DataType;
        public Int32 Length;
        public bool IsNullable;
        public string DefaultValue;

        public string ToColumnDefs()
        {
            if (stringTypes.Contains(this.DataType))
            {
                return String.Format("{0} {1} ({2}) {3}", ColumnName, DataType, Length == -1 ? "MAX" : Length.ToString(), IsNullable ? "NULL" : "NOT NULL");
            }
            else
            {
                return String.Format("{0} {1} {2}", ColumnName, DataType, IsNullable ? "NULL" : "NOT NULL");
            }
        }
    }
}

SqlSchemaModeler.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace System.Data.SqlClient
{
    public class SqlSchemaModeler
    {
        private readonly string connectionString;
        private readonly string[] includedSchemas;

        public SqlSchemaModeler(string connectionString, params string[] includedSchemas)
        {
            this.connectionString = connectionString;
            this.includedSchemas = includedSchemas;
        }

        public List<TableEntity> GetTables()
        {
            var tables = new List<TableEntity>();
            using (var conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (var cmd = new SqlCommand(GetSchemaCommand, conn))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            var xmlData = reader[0] as string;
                            if (xmlData != null)
                            {
                                var xdoc = XDocument.Parse(xmlData);

                                var tablesAndDependencies = GetTableDescriptorsFromXdoc(xdoc);

                                tables.AddRange(tablesAndDependencies.Select(t => t.Key));
                                WireDependencies(tablesAndDependencies);
                            }
                        }
                    }
                }
            }

            SortTablesByDependencies(tables);

            return tables;
        }

        public string BuildDeleteScript(IEnumerable<TableEntity> tableEntities)
        {
            // forced materialization because we are going to enumerate the heck out of this thing... it should be a small set (upper bounds of 1000ish), but this is still a reasonable thing to do.
            tableEntities = tableEntities.ToList();
            var scriptBuilder = new StringBuilder();

            var tableEntitiesThatHaveBeenDeleted = new List<TableEntity>();

            foreach (var tableEntity in tableEntities.Reverse())
            {
                foreach (var dependedUponBy in tableEntity.DependedOnBy.Except(tableEntitiesThatHaveBeenDeleted))
                {
                    var actualRef = tableEntities.First(t => object.ReferenceEquals(t, dependedUponBy));
                    if (actualRef.DependedOnBy.Count == 0)
                    {
                        scriptBuilder.AppendFormat("\tTRUNCATE TABLE [{3}].[{4}] -- Is Depended Upon By: [{0}].[{1}]{2}", tableEntity.SchemaName, tableEntity.TableName, Environment.NewLine, dependedUponBy.SchemaName, dependedUponBy.TableName);
                    }
                    else
                    {
                        scriptBuilder.AppendFormat("\tDELETE FROM [{3}].[{4}] -- Is Depended Upon By: [{0}].[{1}]{2}", tableEntity.SchemaName, tableEntity.TableName, Environment.NewLine, dependedUponBy.SchemaName, dependedUponBy.TableName);
                    }
                    tableEntitiesThatHaveBeenDeleted.Add(dependedUponBy);
                }

                if (tableEntitiesThatHaveBeenDeleted.Contains(tableEntity))
                {
                    scriptBuilder.AppendFormat("\t -- [{0}].[{1}] has already been deleted as a depended upon table{2}", tableEntity.SchemaName, tableEntity.TableName, Environment.NewLine);
                }
                else
                {
                    if (tableEntity.DependedOnBy.Count == 0)
                    {
                        scriptBuilder.AppendFormat("\tTRUNCATE TABLE [{0}].[{1}]{2}", tableEntity.SchemaName, tableEntity.TableName, Environment.NewLine);
                    }
                    else
                    {
                        scriptBuilder.AppendFormat("\tDELETE FROM [{0}].[{1}]{2}", tableEntity.SchemaName, tableEntity.TableName, Environment.NewLine);
                    }
                    tableEntitiesThatHaveBeenDeleted.Add(tableEntity);
                }
            }

            return scriptBuilder.ToString();
        }

        private Dictionary<TableEntity, IEnumerable<XElement>> GetTableDescriptorsFromXdoc(XDocument xdoc)
        {
            if (xdoc == null)
            {
                throw new ArgumentNullException("xdoc");
            }

            return (xdoc.Root == null ? Enumerable.Empty<KeyValuePair<TableEntity, IEnumerable<XElement>>>()
                        : xdoc.Root.Elements("table")
                            .Select(el => new KeyValuePair<TableEntity, IEnumerable<XElement>>(GetTableDescriptorFromXElement(el), el.Elements("dependsUpon").Union(el.Elements("dependedUponBy"))))
                )
                .Where(kvp => this.includedSchemas == null || this.includedSchemas.Contains(kvp.Key.SchemaName))
                .ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
        }

        private TableEntity GetTableDescriptorFromXElement(XElement el)
        {
            return new TableEntity
            {
                SchemaName = el.GetAttributeValueOrDefault("schema"),
                TableName = el.GetAttributeValueOrDefault("name"),
                Columns = GetColumnsFromTableElement(el)
            };
        }

        private List<ColumnEntity> GetColumnsFromTableElement(XElement el)
        {
            return el.Elements("column")
                .Select(GetColumnFromColumnElement)
                .ToList();
        }

        private ColumnEntity GetColumnFromColumnElement(XElement el)
        {
            return new ColumnEntity
            {
                SqlColumnName = el.GetAttributeValueOrDefault("name"),
                ColumnName = string.Concat("[", el.GetAttributeValueOrDefault("name"), "]"),
                DataType = el.GetAttributeValueOrDefault("dataType"),
                IsNullable = el.GetAttributeValueOrDefault<bool>("isNullable"),
                Length = el.GetAttributeValueOrDefault<int>("maxLength"),
                DefaultValue = el.GetAttributeValueOrDefault("defaultValue")
            };
        }

        private void WireDependencies(Dictionary<TableEntity, IEnumerable<XElement>> tablesAndDependencyElements)
        {
            foreach (var kvp in tablesAndDependencyElements)
            {
                var dependencies = kvp.Value
                    .Where(el => el.Name == "dependsUpon")
                    .Select(el => (TableEntity)GetSchemaQualifiedNameFromDependencyElement(el));

                var dependedUponBy = kvp.Value
                    .Where(el => el.Name == "dependedUponBy")
                    .Select(el => (TableEntity)GetSchemaQualifiedNameFromDependencyElement(el));

                kvp.Key.DependsUpon = dependencies.ToList();
                kvp.Key.DependedOnBy = dependedUponBy.ToList();
            }
        }

        private string GetSchemaQualifiedNameFromDependencyElement(XElement el)
        {
            var schemaName = el.GetAttributeValueOrDefault("schemaName");
            var tableName = el.GetAttributeValueOrDefault("tableName");

            return schemaName == null ? tableName : string.Concat(schemaName, ".", tableName);
        }

        private void SortTablesByDependencies(List<TableEntity> tables)
        {
            int thisTableIdx = 0;
            while (thisTableIdx < tables.Count)
            {
                var table = tables.ElementAt(thisTableIdx);

                // no dependencies... move on
                if (!table.DependsUpon.Any())
                {
                    thisTableIdx++;
                    continue;
                }

                // move me to that index...
                var dependentIndecesAfterMe = table.DependsUpon
                    .Select(d => tables.IndexOf(d))
                    .Where(idx => idx > thisTableIdx)
                    .ToList();
                if (!dependentIndecesAfterMe.Any())
                {
                    thisTableIdx++;
                    continue;
                }

                var maxDependentIndex = dependentIndecesAfterMe.Max();

                tables.RemoveAt(thisTableIdx);

                // check that we need to move this to the end of the list (if ++max > count... then we should call .Add, not .Insert)
                if (++maxDependentIndex > tables.Count)
                {
                    tables.Add(table);
                }
                else
                {
                    tables.Insert(maxDependentIndex, table);
                }
            }
        }


        private const string GetSchemaCommand = @"WITH [DependentTables] AS (
	SELECT DISTINCT
	    [TableConstraint].[TABLE_SCHEMA] AS [TableSchema],
	    [TableConstraint].[TABLE_NAME] AS [TableName],
		[ColumnConstraint].[TABLE_SCHEMA] AS [DependsUponSchema],
		[ColumnConstraint].[TABLE_NAME] AS [DependsUponTable]	
	FROM 
		INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE [TableConstraint]
	JOIN
		INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS [ReferentialConstraint] ON
			[ReferentialConstraint].[CONSTRAINT_SCHEMA] = [TableConstraint].[CONSTRAINT_SCHEMA]
			AND [ReferentialConstraint].[CONSTRAINT_NAME] = [TableConstraint].[CONSTRAINT_NAME]
	JOIN
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS [Reference] ON 
			[ReferentialConstraint].[UNIQUE_CONSTRAINT_SCHEMA] = [Reference].[CONSTRAINT_SCHEMA]
			AND [ReferentialConstraint].[UNIQUE_CONSTRAINT_NAME] = [Reference].[CONSTRAINT_NAME]
	JOIN
		INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE [ColumnConstraint] ON
			[ColumnConstraint].[CONSTRAINT_SCHEMA] = [Reference].[CONSTRAINT_SCHEMA]
			AND [ColumnConstraint].[CONSTRAINT_NAME] = [Reference].[CONSTRAINT_NAME]
	WHERE	-- This removes self joined tables (self joined by Foreign Key) 		
		[TableConstraint].[TABLE_SCHEMA] <> [ColumnConstraint].[TABLE_SCHEMA]
		OR [TableConstraint].[TABLE_NAME] <> [ColumnConstraint].[TABLE_NAME]		
)
SELECT
	[Table_Schema] AS ""@schema"",
	[Table_Name] AS ""@name"",	
	(
		SELECT 
			[COLUMN_NAME] AS ""@name"",
			CASE WHEN [IS_NULLABLE] = 'YES' THEN 'True' ELSE 'False' END AS ""@isNullable"",
			[DATA_TYPE] AS ""@dataType"",
			[CHARACTER_MAXIMUM_LENGTH] AS ""@maxLength"",
			[CHARACTER_OCTET_LENGTH] AS ""@charOctetLength"",
			[CHARACTER_SET_NAME] AS ""@charSetName"",
			[COLLATION_NAME] AS ""@collation"",
			[COLUMN_DEFAULT] AS ""@defaultValue""
		FROM 
			INFORMATION_SCHEMA.COLUMNS [Column]
		WHERE
			[Table].[TABLE_SCHEMA] = [Column].[TABLE_SCHEMA]
			AND [Table].[TABLE_NAME] = [Column].[TABLE_NAME]
		ORDER BY
			[ORDINAL_POSITION]
		FOR XML PATH ('column'), TYPE
	),
	(
		SELECT DISTINCT
			[DependsUponSchema] AS ""@schemaName"",
			[DependsUponTable] AS ""@tableName""
		FROM 
			[DependentTables]		
		WHERE
			[TableName] = [Table].[TABLE_NAME]
			AND [TableSchema] = [Table].[TABLE_SCHEMA]
		FOR XML PATH('dependsUpon'), TYPE			
	),
	(
		SELECT DISTINCT
			[TableSchema] AS ""@schemaName"",
			[TableName] AS ""@tableName""
		FROM 
			[DependentTables]		
		WHERE
			[DependsUponTable] = [Table].[TABLE_NAME]
			AND [DependsUponSchema] = [Table].[TABLE_SCHEMA]			
		FOR XML PATH('dependedUponBy'), TYPE			
	)
FROM
	INFORMATION_SCHEMA.TABLES [Table]
FOR XML PATH ('table'), ROOT ('tables'), TYPE";
    }
}

TableEntity.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace System.Data.SqlClient
{
    public class TableEntity
    {
        public string SchemaName { get; set; }
        public string TableName { get; set; }
        public List<ColumnEntity> Columns { get; set; }
        public List<TableEntity> DependsUpon { get; set; }
        public List<TableEntity> DependedOnBy { get; set; }

        public override bool Equals(object obj)
        {
            if (obj == null)
            {
                return false;
            }

            return ReferenceEquals(this, obj) || obj.ToString() == this.ToString();
        }

        public override int GetHashCode()
        {
            return (this.ToString() ?? string.Empty).GetHashCode();
        }

        public override string ToString()
        {
            if (this.SchemaName == null)
            {
                return this.TableName;
            }

            return string.Concat(this.SchemaName, ".", this.TableName);
        }

        public static implicit operator TableEntity(string input)
        {
            var parts = input.Split('.');
            string schemaName = null;
            string tableName = null;
            if (parts.Length > 1)
            {
                schemaName = parts[0];
                tableName = parts[1];
            }
            else if (parts.Length > 0)
            {
                tableName = parts[0];
            }

            return new TableEntity
            {
                TableName = tableName,
                SchemaName = schemaName
            };
        }
    }
}

XDocumentExtensions

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace System.Xml
{
    /// <summary>
    /// 
    /// </summary>
    public static class XDocumentExtensions
    {
        /// <summary>
        /// Gets the attribute value or default.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="el">The el.</param>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public static T GetAttributeValueOrDefault<T>(this XElement el, XName name)
            where T : IConvertible
        {
            var a = el.Attribute(name);
            if (a == null)
            {
                return default(T);
            }

            return (T)Convert.ChangeType(a.Value, typeof(T));
        }

        /// <summary>
        /// Gets the attribute value or default.
        /// </summary>
        /// <param name="el">The el.</param>
        /// <param name="name">The name.</param>
        /// <returns></returns>
        public static string GetAttributeValueOrDefault(this XElement el, XName name)
        {
            var a = el.Attribute(name);
            if (a == null)
            {
                return null;
            }

            return a.Value;
        }
    }
}