Tutorial :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.


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


Solution: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  


Solution:3

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)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »