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.

1: 2005/2008, 2: Nope, 3: thats for V2 :) – mcintyre321 May 27 '09 at 22:50 — mcintyre321, May 27 '09 at 22:50

score 2 · Answer 1 · answered May 27 '09 at 11:56

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

answered May 27 '09 at 11:56

gbn

422,506
82
585
676

i need hashes of tables, sprocs, constraints everything I'm afraid! – mcintyre321 May 27 '09 at 22:53
So an incomplete answer is -1? Given you've already written a tool http://stackoverflow.com/questions/6371/how-do-you-manage-databases-in-development-test-and-production/541419#541419, why did you ask the question? – gbn May 28 '09 at 06:24

Matt Spradley · Accepted Answer · 2009-05-28T01:29:12.587

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

Sorry Matt, just tried it in LinqPad. That seems to script the db itself, but not tables schemas or other objects. Also, sb.GetHashCode() is only a hash value that works for the current AppDomain I think! It returns the memory location of the stringbuilder, not a hash of its value, so next time you run the app the code will be different. new StringBuilder("Hello").GetHashCode() != new StringBuilder("Hello").GetHashCode() — mcintyre321, May 27 '09 at 23:01
You are right. I made that same mistake before. Maybe I have learned this time. I haved edited the code. Hopefully this is closer. There is still the issue of whitespace and determining exactly what details are wanted but this should be closer. — Matt Spradley, May 28 '09 at 01:32

score 0 · Answer 3 · answered May 27 '09 at 11:10

0

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)

answered May 27 '09 at 11:10

devio

36,858
7
80
143

Not bad. SMO is a bit slow for my likeing. Will probably use OpenDbDiff to do something similar as it seems noone has a single SP call I can use. – mcintyre321 May 27 '09 at 23:05

Fastest way to script generation SQL server DB schema for hashing

3 Answers3