Exemplo n.º 1
0
        public void Load()
        {
            var cnStrBuilder = new SqlConnectionStringBuilder(Connection);

            Tables.Clear();
            Routines.Clear();
            ForeignKeys.Clear();
            DataTables.Clear();
            using (var cn = new SqlConnection(Connection))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    // query schema for database properties
                    cm.CommandText = @"
            select
            [compatibility_level],
            [collation_name],
            [is_auto_close_on],
            [is_auto_shrink_on],
            [snapshot_isolation_state],
            [is_read_committed_snapshot_on],
            [recovery_model_desc],
            [page_verify_option_desc],
            [is_auto_create_stats_on],
            [is_auto_update_stats_on],
            [is_auto_update_stats_async_on],
            [is_ansi_null_default_on],
            [is_ansi_nulls_on],
            [is_ansi_padding_on],
            [is_ansi_warnings_on],
            [is_arithabort_on],
            [is_concat_null_yields_null_on],
            [is_numeric_roundabort_on],
            [is_quoted_identifier_on],
            [is_recursive_triggers_on],
            [is_cursor_close_on_commit_on],
            [is_local_cursor_default],
            [is_trustworthy_on],
            [is_db_chaining_on],
            [is_parameterization_forced],
            [is_date_correlation_on]
            from sys.databases
            where name = @dbname
            ";
                    cm.Parameters.AddWithValue("@dbname", cnStrBuilder.InitialCatalog);
                    using (IDataReader dr = cm.ExecuteReader())
                    {
                        if (dr.Read())
                        {
                            SetPropString("COMPATIBILITY_LEVEL", dr["compatibility_level"]);
                            SetPropString("COLLATE", dr["collation_name"]);
                            SetPropOnOff("AUTO_CLOSE", dr["is_auto_close_on"]);
                            SetPropOnOff("AUTO_SHRINK", dr["is_auto_shrink_on"]);
                            if (dr["snapshot_isolation_state"] != DBNull.Value)
                            {
                                FindProp("ALLOW_SNAPSHOT_ISOLATION").Value =
                                    (byte)dr["snapshot_isolation_state"] == 0 ||
                                    (byte)dr["snapshot_isolation_state"] == 2
                                        ? "OFF"
                                        : "ON";
                            }
                            SetPropOnOff("READ_COMMITTED_SNAPSHOT", dr["is_read_committed_snapshot_on"]);
                            SetPropString("RECOVERY", dr["recovery_model_desc"]);
                            SetPropString("PAGE_VERIFY", dr["page_verify_option_desc"]);
                            SetPropOnOff("AUTO_CREATE_STATISTICS", dr["is_auto_create_stats_on"]);
                            SetPropOnOff("AUTO_UPDATE_STATISTICS", dr["is_auto_update_stats_on"]);
                            SetPropOnOff("AUTO_UPDATE_STATISTICS_ASYNC", dr["is_auto_update_stats_async_on"]);
                            SetPropOnOff("ANSI_NULL_DEFAULT", dr["is_ansi_null_default_on"]);
                            SetPropOnOff("ANSI_NULLS", dr["is_ansi_nulls_on"]);
                            SetPropOnOff("ANSI_PADDING", dr["is_ansi_padding_on"]);
                            SetPropOnOff("ANSI_WARNINGS", dr["is_ansi_warnings_on"]);
                            SetPropOnOff("ARITHABORT", dr["is_arithabort_on"]);
                            SetPropOnOff("CONCAT_NULL_YIELDS_NULL", dr["is_concat_null_yields_null_on"]);
                            SetPropOnOff("NUMERIC_ROUNDABORT", dr["is_numeric_roundabort_on"]);
                            SetPropOnOff("QUOTED_IDENTIFIER", dr["is_quoted_identifier_on"]);
                            SetPropOnOff("RECURSIVE_TRIGGERS", dr["is_recursive_triggers_on"]);
                            SetPropOnOff("CURSOR_CLOSE_ON_COMMIT", dr["is_cursor_close_on_commit_on"]);
                            if (dr["is_local_cursor_default"] != DBNull.Value)
                            {
                                FindProp("CURSOR_DEFAULT").Value =
                                    (bool)dr["is_local_cursor_default"] ? "LOCAL" : "GLOBAL";
                            }
                            SetPropOnOff("TRUSTWORTHY", dr["is_trustworthy_on"]);
                            SetPropOnOff("DB_CHAINING", dr["is_db_chaining_on"]);
                            if (dr["is_parameterization_forced"] != DBNull.Value)
                            {
                                FindProp("PARAMETERIZATION").Value =
                                    (bool)dr["is_parameterization_forced"] ? "FORCED" : "SIMPLE";
                            }
                            SetPropOnOff("DATE_CORRELATION_OPTIMIZATION", dr["is_date_correlation_on"]);
                        }
                    }

                    //get schemas
                    cm.CommandText = @"
            select s.name as schemaName, p.name as principalName
            from sys.schemas s
            inner join sys.database_principals p on s.principal_id = p.principal_id
            where s.schema_id < 16384
            and s.name not in ('dbo','guest','sys','INFORMATION_SCHEMA')
            order by schema_id
            ";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Schemas.Add(new Schema((string)dr["schemaName"], (string)dr["principalName"]));
                        }
                    }

                    //get tables
                    cm.CommandText = @"
                    select
                        TABLE_SCHEMA,
                        TABLE_NAME
                    from INFORMATION_SCHEMA.TABLES
                    where TABLE_TYPE = 'BASE TABLE'";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Tables.Add(new Table((string)dr["TABLE_SCHEMA"], (string)dr["TABLE_NAME"]));
                        }
                    }

                    //get columns
                    cm.CommandText = @"
                    select
                        t.TABLE_SCHEMA,
                        c.TABLE_NAME,
                        c.COLUMN_NAME,
                        c.DATA_TYPE,
                        c.IS_NULLABLE,
                        c.CHARACTER_MAXIMUM_LENGTH,
                        c.NUMERIC_PRECISION,
                        c.NUMERIC_SCALE
                    from INFORMATION_SCHEMA.COLUMNS c
                        inner join INFORMATION_SCHEMA.TABLES t
                                on t.TABLE_NAME = c.TABLE_NAME
                                    and t.TABLE_SCHEMA = c.TABLE_SCHEMA
                                    and t.TABLE_CATALOG = c.TABLE_CATALOG
                    where
                        t.TABLE_TYPE = 'BASE TABLE'
            ";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var c = new Column();
                            c.Name = (string)dr["COLUMN_NAME"];
                            c.Type = (string)dr["DATA_TYPE"];
                            c.IsNullable = (string)dr["IS_NULLABLE"] == "YES";

                            switch (c.Type)
                            {
                                case "binary":
                                case "char":
                                case "nchar":
                                case "nvarchar":
                                case "varbinary":
                                case "varchar":
                                    c.Length = (int)dr["CHARACTER_MAXIMUM_LENGTH"];
                                    break;
                                case "decimal":
                                case "numeric":
                                    c.Precision = (byte)dr["NUMERIC_PRECISION"];
                                    c.Scale = (int)dr["NUMERIC_SCALE"];
                                    break;
                            }

                            FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]).Columns.Add(c);
                        }
                    }

                    //get column identities
                    cm.CommandText = @"
                    select
                        s.name as TABLE_SCHEMA,
                        t.name as TABLE_NAME,
                        c.name AS COLUMN_NAME,
                        i.SEED_VALUE, i.INCREMENT_VALUE
                    from sys.tables t
                        inner join sys.columns c on c.object_id = t.object_id
                        inner join sys.identity_columns i on i.object_id = c.object_id
                            and i.column_id = c.column_id
                        inner join sys.schemas s on s.schema_id = t.schema_id ";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            try
                            {
                                Table t = FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]);
                                Column c = t.Columns.Find((string)dr["COLUMN_NAME"]);
                                string seed = dr["SEED_VALUE"].ToString();
                                string increment = dr["INCREMENT_VALUE"].ToString();
                                c.Identity = new Identity(seed, increment);
                            }
                            catch (Exception ex)
                            {
                                throw new ApplicationException(string.Format("{0}.{1} : {2}", dr["TABLE_SCHEMA"], dr["TABLE_NAME"], ex.Message), ex);
                            }
                        }
                    }

                    //get column defaults
                    cm.CommandText = @"
                    select
                        s.name as TABLE_SCHEMA,
                        t.name as TABLE_NAME,
                        c.name as COLUMN_NAME,
                        d.name as DEFAULT_NAME,
                        d.definition as DEFAULT_VALUE
                    from sys.tables t
                        inner join sys.columns c on c.object_id = t.object_id
                        inner join sys.default_constraints d on c.column_id = d.parent_column_id
                            and d.parent_object_id = c.object_id
                        inner join sys.schemas s on s.schema_id = t.schema_id";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Table t = FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]);
                            t.Columns.Find((string)dr["COLUMN_NAME"]).Default =
                                new Default((string)dr["DEFAULT_NAME"], (string)dr["DEFAULT_VALUE"]);
                        }
                    }

                    //get constraints & indexes
                    cm.CommandText = @"
                    select
                        s.name as schemaName,
                        t.name as tableName,
                        i.name as indexName,
                        c.name as columnName,
                        i.is_primary_key,
                        i.is_unique_constraint,
                        i.is_unique,
                        i.type_desc,
                        isnull(ic.is_included_column, 0) as is_included_column
                    from sys.tables t
                        inner join sys.indexes i on i.object_id = t.object_id
                        inner join sys.index_columns ic on ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                        inner join sys.columns c on c.object_id = t.object_id
                            and c.column_id = ic.column_id
                        inner join sys.schemas s on s.schema_id = t.schema_id
                    order by s.name, t.name, i.name, ic.key_ordinal, ic.index_column_id";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Table t = FindTable((string)dr["tableName"], (string)dr["schemaName"]);
                            Constraint c = t.FindConstraint((string)dr["indexName"]);
                            if (c == null)
                            {
                                c = new Constraint((string)dr["indexName"], "", "");
                                t.Constraints.Add(c);
                                c.Table = t;
                            }
                            c.Clustered = (string)dr["type_desc"] == "CLUSTERED";
                            c.Unique = (bool)dr["is_unique"];
                            if ((bool)dr["is_included_column"])
                            {
                                c.IncludedColumns.Add((string)dr["columnName"]);
                            }
                            else
                            {
                                c.Columns.Add((string)dr["columnName"]);
                            }

                            c.Type = "INDEX";
                            if ((bool)dr["is_primary_key"]) c.Type = "PRIMARY KEY";
                            if ((bool)dr["is_unique_constraint"]) c.Type = "UNIQUE";
                        }
                    }

                    //get foreign keys
                    cm.CommandText = @"
                    select
                        TABLE_SCHEMA,
                        TABLE_NAME,
                        CONSTRAINT_NAME
                    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    where CONSTRAINT_TYPE = 'FOREIGN KEY'";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Table t = FindTable((string)dr["TABLE_NAME"], (string)dr["TABLE_SCHEMA"]);
                            var fk = new ForeignKey((string)dr["CONSTRAINT_NAME"]);
                            fk.Table = t;
                            ForeignKeys.Add(fk);
                        }
                    }

                    //get foreign key props
                    cm.CommandText = @"
            select
            CONSTRAINT_NAME,
            UPDATE_RULE,
            DELETE_RULE,
            fk.is_disabled
            from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
            inner join sys.foreign_keys fk on rc.CONSTRAINT_NAME = fk.name";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            ForeignKey fk = FindForeignKey((string)dr["CONSTRAINT_NAME"]);
                            fk.OnUpdate = (string)dr["UPDATE_RULE"];
                            fk.OnDelete = (string)dr["DELETE_RULE"];
                            fk.Check = !(bool)dr["is_disabled"];
                        }
                    }

                    //get foreign key columns and ref table
                    cm.CommandText = @"
            select
            fk.name as CONSTRAINT_NAME,
            c1.name as COLUMN_NAME,
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) as REF_TABLE_SCHEMA,
            OBJECT_NAME(fk.referenced_object_id) as REF_TABLE_NAME,
            c2.name as REF_COLUMN_NAME
            from sys.foreign_keys fk
            inner join sys.foreign_key_columns fkc
            on fkc.constraint_object_id = fk.object_id
            inner join sys.columns c1
            on fkc.parent_column_id = c1.column_id
            and fkc.parent_object_id = c1.object_id
            inner join sys.columns c2
            on fkc.referenced_column_id = c2.column_id
            and fkc.referenced_object_id = c2.object_id
            order by fk.name
            ";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            ForeignKey fk = FindForeignKey((string)dr["CONSTRAINT_NAME"]);
                            if (fk == null)
                            {
                                continue;
                            }
                            fk.Columns.Add((string)dr["COLUMN_NAME"]);
                            fk.RefColumns.Add((string)dr["REF_COLUMN_NAME"]);
                            if (fk.RefTable == null)
                            {
                                fk.RefTable = FindTable((string)dr["REF_TABLE_NAME"], (string)dr["REF_TABLE_SCHEMA"]);
                            }
                        }
                    }

                    //get routines
                    cm.CommandText = @"
                    select
                        s.name as schemaName,
                        o.name as routineName,
                        o.type_desc,
                        m.definition,
                        m.uses_ansi_nulls,
                        m.uses_quoted_identifier,
                        t.name as tableName
                    from sys.sql_modules m
                        inner join sys.objects o on m.object_id = o.object_id
                        inner join sys.schemas s on s.schema_id = o.schema_id
                        left join sys.triggers tr on m.object_id = tr.object_id
                        left join sys.tables t on tr.parent_id = t.object_id";
                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            var r = new Routine((string)dr["schemaName"], (string)dr["routineName"]);
                            r.Text = (string)dr["definition"];
                            r.AnsiNull = (bool)dr["uses_ansi_nulls"];
                            r.QuotedId = (bool)dr["uses_quoted_identifier"];
                            Routines.Add(r);

                            switch ((string)dr["type_desc"])
                            {
                                case "SQL_STORED_PROCEDURE":
                                    r.Type = "PROCEDURE";
                                    break;
                                case "SQL_TRIGGER":
                                    r.Type = "TRIGGER";
                                    break;
                                case "SQL_SCALAR_FUNCTION":
                                    r.Type = "FUNCTION";
                                    break;
                                case "VIEW":
                                    r.Type = "VIEW";
                                    break;
                            }
                        }
                    }
                }
            }
        }
Exemplo n.º 2
0
 public ColumnDiff Compare(Column c, CompareConfig compareConfig)
 {
     return new ColumnDiff(this, c, compareConfig);
 }
Exemplo n.º 3
0
        public void Load()
        {
            var cnStrBuilder = new SqlConnectionStringBuilder(Connection);

            Tables.Clear();
            Routines.Clear();
            ForeignKeys.Clear();
            DataTables.Clear();
            using (var cn = new SqlConnection(Connection)) {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand()) {
                    // query schema for database properties
                    cm.CommandText = @"
            select
            [compatibility_level],
            [collation_name],
            [is_auto_close_on],
            [is_auto_shrink_on],
            [snapshot_isolation_state],
            [is_read_committed_snapshot_on],
            [recovery_model_desc],
            [page_verify_option_desc],
            [is_auto_create_stats_on],
            [is_auto_update_stats_on],
            [is_auto_update_stats_async_on],
            [is_ansi_null_default_on],
            [is_ansi_nulls_on],
            [is_ansi_padding_on],
            [is_ansi_warnings_on],
            [is_arithabort_on],
            [is_concat_null_yields_null_on],
            [is_numeric_roundabort_on],
            [is_quoted_identifier_on],
            [is_recursive_triggers_on],
            [is_cursor_close_on_commit_on],
            [is_local_cursor_default],
            [is_trustworthy_on],
            [is_db_chaining_on],
            [is_parameterization_forced],
            [is_date_correlation_on]
            from sys.databases
            where name = @dbname
            ";
                    cm.Parameters.AddWithValue("@dbname", cnStrBuilder.InitialCatalog);
                    using (IDataReader dr = cm.ExecuteReader()) {
                        if (dr.Read()) {
                            SetPropString("COMPATIBILITY_LEVEL", dr["compatibility_level"]);
                            SetPropString("COLLATE", dr["collation_name"]);
                            SetPropOnOff("AUTO_CLOSE", dr["is_auto_close_on"]);
                            SetPropOnOff("AUTO_SHRINK", dr["is_auto_shrink_on"]);
                            if (dr["snapshot_isolation_state"] != DBNull.Value) {
                                FindProp("ALLOW_SNAPSHOT_ISOLATION").Value = (byte) dr["snapshot_isolation_state"] == 0 ||
                                                                             (byte) dr["snapshot_isolation_state"] == 2
                                    ? "OFF"
                                    : "ON";
                            }
                            SetPropOnOff("READ_COMMITTED_SNAPSHOT", dr["is_read_committed_snapshot_on"]);
                            SetPropString("RECOVERY", dr["recovery_model_desc"]);
                            SetPropString("PAGE_VERIFY", dr["page_verify_option_desc"]);
                            SetPropOnOff("AUTO_CREATE_STATISTICS", dr["is_auto_create_stats_on"]);
                            SetPropOnOff("AUTO_UPDATE_STATISTICS", dr["is_auto_update_stats_on"]);
                            SetPropOnOff("AUTO_UPDATE_STATISTICS_ASYNC", dr["is_auto_update_stats_async_on"]);
                            SetPropOnOff("ANSI_NULL_DEFAULT", dr["is_ansi_null_default_on"]);
                            SetPropOnOff("ANSI_NULLS", dr["is_ansi_nulls_on"]);
                            SetPropOnOff("ANSI_PADDING", dr["is_ansi_padding_on"]);
                            SetPropOnOff("ANSI_WARNINGS", dr["is_ansi_warnings_on"]);
                            SetPropOnOff("ARITHABORT", dr["is_arithabort_on"]);
                            SetPropOnOff("CONCAT_NULL_YIELDS_NULL", dr["is_concat_null_yields_null_on"]);
                            SetPropOnOff("NUMERIC_ROUNDABORT", dr["is_numeric_roundabort_on"]);
                            SetPropOnOff("QUOTED_IDENTIFIER", dr["is_quoted_identifier_on"]);
                            SetPropOnOff("RECURSIVE_TRIGGERS", dr["is_recursive_triggers_on"]);
                            SetPropOnOff("CURSOR_CLOSE_ON_COMMIT", dr["is_cursor_close_on_commit_on"]);
                            if (dr["is_local_cursor_default"] != DBNull.Value) {
                                FindProp("CURSOR_DEFAULT").Value = (bool) dr["is_local_cursor_default"] ? "LOCAL" : "GLOBAL";
                            }
                            SetPropOnOff("TRUSTWORTHY", dr["is_trustworthy_on"]);
                            SetPropOnOff("DB_CHAINING", dr["is_db_chaining_on"]);
                            if (dr["is_parameterization_forced"] != DBNull.Value) {
                                FindProp("PARAMETERIZATION").Value = (bool) dr["is_parameterization_forced"] ? "FORCED" : "SIMPLE";
                            }
                            SetPropOnOff("DATE_CORRELATION_OPTIMIZATION", dr["is_date_correlation_on"]);
                        }
                    }

                    //get schemas
                    cm.CommandText = @"
            select s.name as schemaName, p.name as principalName
            from sys.schemas s
            inner join sys.database_principals p on s.principal_id = p.principal_id
            where s.schema_id < 16384
            and s.name not in ('dbo','guest','sys','INFORMATION_SCHEMA')
            order by schema_id
            ";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            Schemas.Add(new Schema((string) dr["schemaName"], (string) dr["principalName"]));
                        }
                    }

                    //get tables
                    cm.CommandText = @"
                    select
                        TABLE_SCHEMA,
                        TABLE_NAME
                    from INFORMATION_SCHEMA.TABLES
                    where TABLE_TYPE = 'BASE TABLE'";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            Tables.Add(new Table((string) dr["TABLE_SCHEMA"], (string) dr["TABLE_NAME"]));
                        }
                    }

                    //get columns
                    cm.CommandText = @"
                    select
                        t.TABLE_SCHEMA,
                        c.TABLE_NAME,
                        c.COLUMN_NAME,
                        c.DATA_TYPE,
                        c.IS_NULLABLE,
                        c.CHARACTER_MAXIMUM_LENGTH,
                        c.NUMERIC_PRECISION,
                        c.NUMERIC_SCALE
                    from INFORMATION_SCHEMA.COLUMNS c
                        inner join INFORMATION_SCHEMA.TABLES t
                                on t.TABLE_NAME = c.TABLE_NAME
                                    and t.TABLE_SCHEMA = c.TABLE_SCHEMA
                                    and t.TABLE_CATALOG = c.TABLE_CATALOG
                    where
                        t.TABLE_TYPE = 'BASE TABLE'
            ";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            var c = new Column();
                            c.Name = (string) dr["COLUMN_NAME"];
                            c.Type = (string) dr["DATA_TYPE"];
                            c.IsNullable = (string) dr["IS_NULLABLE"] == "YES";

                            switch (c.Type) {
                                case "binary":
                                case "char":
                                case "nchar":
                                case "nvarchar":
                                case "varbinary":
                                case "varchar":
                                    c.Length = (int) dr["CHARACTER_MAXIMUM_LENGTH"];
                                    break;
                                case "decimal":
                                case "numeric":
                                    c.Precision = (byte) dr["NUMERIC_PRECISION"];
                                    c.Scale = (int) dr["NUMERIC_SCALE"];
                                    break;
                            }

                            FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]).Columns.Add(c);
                        }
                    }

                    //get column identities
                    cm.CommandText = @"
                    select
                        s.name as TABLE_SCHEMA,
                        t.name as TABLE_NAME,
                        c.name AS COLUMN_NAME,
                        i.SEED_VALUE, i.INCREMENT_VALUE
                    from sys.tables t
                        inner join sys.columns c on c.object_id = t.object_id
                        inner join sys.identity_columns i on i.object_id = c.object_id
                            and i.column_id = c.column_id
                        inner join sys.schemas s on s.schema_id = t.schema_id ";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            try {
                                Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]);
                                Column c = t.Columns.Find((string) dr["COLUMN_NAME"]);
                                string seed = dr["SEED_VALUE"].ToString();
                                string increment = dr["INCREMENT_VALUE"].ToString();
                                c.Identity = new Identity(seed, increment);
                            } catch (Exception ex) {
                                throw new ApplicationException(
                                    string.Format("{0}.{1} : {2}", dr["TABLE_SCHEMA"], dr["TABLE_NAME"], ex.Message), ex);
                            }
                        }
                    }

                    //get column defaults
                    cm.CommandText = @"
                    select
                        s.name as TABLE_SCHEMA,
                        t.name as TABLE_NAME,
                        c.name as COLUMN_NAME,
                        d.name as DEFAULT_NAME,
                        d.definition as DEFAULT_VALUE
                    from sys.tables t
                        inner join sys.columns c on c.object_id = t.object_id
                        inner join sys.default_constraints d on c.column_id = d.parent_column_id
                            and d.parent_object_id = c.object_id
                        inner join sys.schemas s on s.schema_id = t.schema_id";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]);
                            t.Columns.Find((string) dr["COLUMN_NAME"]).Default = new Default((string) dr["DEFAULT_NAME"],
                                (string) dr["DEFAULT_VALUE"]);
                        }
                    }

                    //get constraints & indexes
                    cm.CommandText = @"
                    select
                        s.name as schemaName,
                        t.name as tableName,
                        t.baseType,
                        i.name as indexName,
                        c.name as columnName,
                        i.is_primary_key,
                        i.is_unique_constraint,
                        i.is_unique,
                        i.type_desc,
                        isnull(ic.is_included_column, 0) as is_included_column
                    from (
                        select object_id, name, schema_id, 'T' as baseType
                        from   sys.tables
                        union
                        select object_id, name, schema_id, 'V' as baseType
                        from   sys.views
                        ) t
                        inner join sys.indexes i on i.object_id = t.object_id
                        inner join sys.index_columns ic on ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                        inner join sys.columns c on c.object_id = t.object_id
                            and c.column_id = ic.column_id
                        inner join sys.schemas s on s.schema_id = t.schema_id
                    order by s.name, t.name, i.name, ic.key_ordinal, ic.index_column_id";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            Table t = (string) dr["baseType"] == "V"
                                ? new Table((string) dr["schemaName"], (string) dr["tableName"])
                                : FindTable((string) dr["tableName"], (string) dr["schemaName"]);
                            Constraint c = t.FindConstraint((string) dr["indexName"]);
                            if (c == null) {
                                c = new Constraint((string) dr["indexName"], "", "");
                                t.Constraints.Add(c);
                                c.Table = t;

                                if ((string) dr["baseType"] == "V")
                                    ViewIndexes.Add(c);
                            }
                            c.Clustered = (string) dr["type_desc"] == "CLUSTERED";
                            c.Unique = (bool) dr["is_unique"];
                            if ((bool) dr["is_included_column"]) {
                                c.IncludedColumns.Add((string) dr["columnName"]);
                            } else {
                                c.Columns.Add((string) dr["columnName"]);
                            }

                            c.Type = "INDEX";
                            if ((bool) dr["is_primary_key"])
                                c.Type = "PRIMARY KEY";
                            if ((bool) dr["is_unique_constraint"])
                                c.Type = "UNIQUE";
                        }
                    }

                    //get foreign keys
                    cm.CommandText = @"
                    select
                        TABLE_SCHEMA,
                        TABLE_NAME,
                        CONSTRAINT_NAME
                    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    where CONSTRAINT_TYPE = 'FOREIGN KEY'";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]);
                            var fk = new ForeignKey((string) dr["CONSTRAINT_NAME"]);
                            fk.Table = t;
                            ForeignKeys.Add(fk);
                        }
                    }

                    //get foreign key props
                    cm.CommandText = @"
            select
            CONSTRAINT_NAME,
            UPDATE_RULE,
            DELETE_RULE,
            fk.is_disabled
            from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
            inner join sys.foreign_keys fk on rc.CONSTRAINT_NAME = fk.name";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            ForeignKey fk = FindForeignKey((string) dr["CONSTRAINT_NAME"]);
                            fk.OnUpdate = (string) dr["UPDATE_RULE"];
                            fk.OnDelete = (string) dr["DELETE_RULE"];
                            fk.Check = !(bool) dr["is_disabled"];
                        }
                    }

                    //get foreign key columns and ref table
                    cm.CommandText = @"
            select
            fk.name as CONSTRAINT_NAME,
            c1.name as COLUMN_NAME,
            OBJECT_SCHEMA_NAME(fk.referenced_object_id) as REF_TABLE_SCHEMA,
            OBJECT_NAME(fk.referenced_object_id) as REF_TABLE_NAME,
            c2.name as REF_COLUMN_NAME
            from sys.foreign_keys fk
            inner join sys.foreign_key_columns fkc
            on fkc.constraint_object_id = fk.object_id
            inner join sys.columns c1
            on fkc.parent_column_id = c1.column_id
            and fkc.parent_object_id = c1.object_id
            inner join sys.columns c2
            on fkc.referenced_column_id = c2.column_id
            and fkc.referenced_object_id = c2.object_id
            order by fk.name, fkc.constraint_column_id
            ";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            ForeignKey fk = FindForeignKey((string) dr["CONSTRAINT_NAME"]);
                            if (fk == null) {
                                continue;
                            }
                            fk.Columns.Add((string) dr["COLUMN_NAME"]);
                            fk.RefColumns.Add((string) dr["REF_COLUMN_NAME"]);
                            if (fk.RefTable == null) {
                                fk.RefTable = FindTable((string) dr["REF_TABLE_NAME"], (string) dr["REF_TABLE_SCHEMA"]);
                            }
                        }
                    }

                    //get routines
                    cm.CommandText = @"
                    select
                        s.name as schemaName,
                        o.name as routineName,
                        o.type_desc,
                        m.definition,
                        m.uses_ansi_nulls,
                        m.uses_quoted_identifier,
                        t.name as tableName
                    from sys.sql_modules m
                        inner join sys.objects o on m.object_id = o.object_id
                        inner join sys.schemas s on s.schema_id = o.schema_id
                        left join sys.triggers tr on m.object_id = tr.object_id
                        left join sys.tables t on tr.parent_id = t.object_id";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            if (dr["definition"] is DBNull) {
                                Console.ForegroundColor = ConsoleColor.Magenta;
                                Console.WriteLine("Warning: Unable to get definition for {0} {1}.{2}", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]);
                                Console.ForegroundColor = ConsoleColor.White;
                            } else {
                                if (!((string)dr["definition"]).Contains((string)dr["routineName"])) {
                                    Console.ForegroundColor = ConsoleColor.Magenta;
                                    Console.WriteLine("Warning: {0} {1}.{2} has been renamed since it's definition.", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]);
                                    Console.ForegroundColor = ConsoleColor.White;
                                }
                                var r = new Routine((string)dr["schemaName"], (string)dr["routineName"]);
                                r.Text = (string)dr["definition"];
                                r.AnsiNull = (bool)dr["uses_ansi_nulls"];
                                r.QuotedId = (bool)dr["uses_quoted_identifier"];
                                Routines.Add(r);

                                switch ((string)dr["type_desc"]) {
                                    case "SQL_STORED_PROCEDURE":
                                        r.RoutineType = Routine.RoutineKind.Procedure;
                                        break;
                                    case "SQL_TRIGGER":
                                        r.RoutineType = Routine.RoutineKind.Trigger;
                                        break;
                                    case "SQL_SCALAR_FUNCTION":
                                    case "SQL_INLINE_TABLE_VALUED_FUNCTION":
                                        r.RoutineType = Routine.RoutineKind.Function;
                                        break;
                                    case "VIEW":
                                        r.RoutineType = Routine.RoutineKind.View;
                                        break;
                                }
                            }
                        }
                    }

                    // get xml schemas
                    cm.CommandText =
                        @"select s.name as DBSchemaName, x.name as XMLSchemaCollectionName, xml_schema_namespace(s.name, x.name) as definition
            from sys.xml_schema_collections x
            inner join sys.schemas s on s.schema_id = x.schema_id
            where s.name != 'sys'";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            var r = new Routine((string) dr["DBSchemaName"], (string) dr["XMLSchemaCollectionName"]) {
                                Text =
                                    string.Format("CREATE XML SCHEMA COLLECTION {0}.{1} AS N'{2}'", dr["DBSchemaName"],
                                        dr["XMLSchemaCollectionName"], dr["definition"]),
                                RoutineType = Routine.RoutineKind.XmlSchemaCollection
                            };
                            Routines.Add(r);
                        }
                    }

                    // get CLR assemblies
                    cm.CommandText = @"select a.name as AssemblyName, a.permission_set_desc, af.name as FileName, af.content
            from sys.assemblies a
            inner join sys.assembly_files af on a.assembly_id = af.assembly_id
            where a.is_user_defined = 1
            order by a.name, af.file_id";
                    SqlAssembly a = null;
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            if (a == null || a.Name != (string) dr["AssemblyName"])
                                a = new SqlAssembly((string) dr["permission_set_desc"], (string) dr["AssemblyName"]);
                            a.Files.Add(new KeyValuePair<string, byte[]>((string) dr["FileName"], (byte[]) dr["content"]));
                            if (!Assemblies.Contains(a))
                                Assemblies.Add(a);
                        }
                    }

                    // get users that have access to the database
                    cm.CommandText =
                        @"select dp.name as UserName, USER_NAME(drm.role_principal_id) as AssociatedDBRole, default_schema_name
            from sys.database_principals dp
            left outer join sys.database_role_members drm on dp.principal_id = drm.member_principal_id
            where dp.type_desc = 'SQL_USER'
            and dp.sid not in (0x00, 0x01) --ignore guest and dbo
            and dp.is_fixed_role = 0
            order by dp.name";
                    SqlUser u = null;
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            if (u == null || u.Name != (string) dr["UserName"])
                                u = new SqlUser((string) dr["UserName"], (string) dr["default_schema_name"]);
                            if (!(dr["AssociatedDBRole"] is DBNull))
                                u.DatabaseRoles.Add((string) dr["AssociatedDBRole"]);
                            if (!Users.Contains(u))
                                Users.Add(u);
                        }
                    }

                    // get sql logins
                    cm.CommandText = @"select sp.name,  sl.password_hash
            from sys.server_principals sp
            inner join sys.sql_logins sl on sp.principal_id = sl.principal_id and sp.type_desc = 'SQL_LOGIN'
            where sp.name not like '##%##'
            and sp.name != 'SA'
            order by sp.name";
                    using (SqlDataReader dr = cm.ExecuteReader()) {
                        while (dr.Read()) {
                            u = Users.SingleOrDefault(user => user.Name == (string) dr["name"]);
                            if (u != null && !(dr["password_hash"] is DBNull))
                                u.PasswordHash = (byte[]) dr["password_hash"];
                        }
                    }
                }
            }
        }
Exemplo n.º 4
0
        public ColumnDiff(Column target, Column source, CompareConfig compareConfig)
        {
            _compareConfig = compareConfig;

            Source = source;
            Target = target;
        }
Exemplo n.º 5
0
 public void Remove(Column c)
 {
     mItems.Remove(c);
 }
Exemplo n.º 6
0
 public void Add(Column c)
 {
     mItems.Add(c);
 }
Exemplo n.º 7
0
 public ColumnDiff Compare(Column c)
 {
     return new ColumnDiff(this, c);
 }
Exemplo n.º 8
0
 public ColumnDiff(Column target, Column source)
 {
     Source = source;
     Target = target;
 }