Fastest way to script generation SQL server DB schema for hashing

Question

I'd like to ge the whole SQL schema for a DB, then generate a hash of it. This is so that I can check if a rollback script returns the schema to it original state. Is there a SP I can use or some other cunning method? I'd like it to be as fast as possible.

Answer 1

I wrote a tool called SMOscript which uses the SMO library calls to script all objects in a database. You can use it to create a single .sql file, and find another tool to compute a hash on the result file. (random google brings up this for example)

Answer 2

If you separate tables and keys from the code and constraints, then you can hash the latter easily.

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

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()));