The fastest way to create a hash SQL DB DB schema generation script

Question

I would like to highlight the entire SQL schema for the database, and then create a hash of this. This is so that I can check if the rollback script returns the schema to its original state. Is there a joint venture that I can use, or some other tricky method? I would like it to be as fast as possible.

Answer 1

The following should work:

        Microsoft.SqlServer.Management.Smo.Server srv = new Microsoft.SqlServer.Management.Smo.Server("Server");

        Microsoft.SqlServer.Management.Smo.Database db = srv.Databases["DB_Name"];

        // Set scripting options as needed using a ScriptingOptions object.
        Microsoft.SqlServer.Management.Smo.ScriptingOptions so = new ScriptingOptions();
        so.AllowSystemObjects = false;
        so.ScriptDrops = false;
        so.Indexes = true;
        so.ClusteredIndexes = true;
        so.PrimaryObject = true;
        so.SchemaQualify = true;
        so.IncludeIfNotExists = false;
        so.Triggers = true;

        System.Collections.Specialized.StringCollection sc = new System.Collections.Specialized.StringCollection();
        StringBuilder sb = new StringBuilder();

        foreach (Table item in db.Tables)
            if (!item.IsSystemObject)
            {
                sc = item.Script(so);
                foreach (string s in sc)
                    sb.Append(s);
            }

        foreach (StoredProcedure item in db.StoredProcedures)
            if (!item.IsSystemObject)
                if (!item.IsSystemObject)
                {
                    sc = item.Script(so);
                    foreach (string s in sc)
                        sb.Append(s);
                }

        foreach (UserDefinedFunction item in db.UserDefinedFunctions)
            if (!item.IsSystemObject)
                if (!item.IsSystemObject)
                {
                    sc = item.Script(so);
                    foreach (string s in sc)
                        sb.Append(s);
                }

        foreach (Trigger item in db.Triggers)
            if (!item.IsSystemObject)
                if (!item.IsSystemObject)
                {
                    sc = item.Script(so);
                    foreach (string s in sc)
                        sb.Append(s);
                }


        //sb.GetHashCode();
        // For a better hash do this.
        System.Security.Cryptography.MD5CryptoServiceProvider hashProvider = new System.Security.Cryptography.MD5CryptoServiceProvider();

        byte[] hashData = hashProvider.ComputeHash(ASCIIEncoding.ASCII.GetBytes(sb.ToString()));

Answer 2

If you separate tables and code keys and restrictions, then you can easily remove the hash.

SELECT
    CHECKSUM_AGG(BINARY_CHECKSUM (*))
FROM   
    (SELECT
        definition 
    FROM
        sys.default_constraints
    UNION ALL
    SELECT
        definition 
    FROM
        sys.sql_modules
    UNION ALL
    SELECT
        definition 
    FROM
        sys.check_constraints
    ) foo

Answer 3

I wrote a tool called SMOscript that uses SMO library calls to script all objects in the database. You can use it to create one .sql file and find another tool to calculate the hash in the result file. (random google calls this one for example)