我想获取整个数据库的SQL模式,然后生成一个哈希值。这样我就可以检查回滚脚本是否将模式返回到原始状态。是否有我可以使用的存储过程或其他巧妙方法?我希望它尽可能快。
如果您将表和键与代码和约束分开,则可以轻松地对后者进行哈希。
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
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()));