自动输出SQLServer存储过程依赖列表到EXCEL文件
需求 现在数据库很老很大，表不多，200来个，但数据量很大：最大的数据表2亿6千万条，每天增加50多w，925个存储过程。 系统大，耦合度很高，牵一发而动全身。人员变动频繁，接手的人员要在修改之前，就得花相当长的时间来分析关联性。 所以，印度资深系统分析员要求我在一个EXCEL文件中，把925个存储过程的所有依赖的对象(表、函数、视图、存储过程等等)都列举出来。 分析 手工逐个打开存储过程去做，对写软件的人来说是很傻的事情，一般重复性工作，如果预计耗时超过3分钟，我就会卷起袖子写个代码。 工作内容有3部分： 1.获取所有的存储过程。我们可以用sysobjects这个系统表，它存储了所有的表、存储过程、视图、函数等。其中存储过程的xtype是P。另外说一下，如果是CLR存储过程，类型是PC。 2. 获取某存储过程所依赖的对象，当然是先google了。很久之前我就知道可以用系统存储过程sp_depends来获取，不过还是应该看看还有什么更好的办法。首先我发现这个：http://www.mssqltips.com/tip.asp?tip=1294 。作者研究出4种办法：INFORMATION_SCHEMA.ROUTINES/sp_depends/syscomments/sp_MSdependencies。其中就有我一直在用的sp_depends。其它办法有的霸王硬上弓：用charindex来遍历存储过程内容，或者用LIKE来判断。。。。。我服了，写代码的风格千差万别，一些是[Foo]，一些是Foo，而且不同的存储过程名称可能存在完全给另外一个包含，譬如Foo Foo1 AFoo等。 看完之后，我还是觉得使用sp_depends相对靠谱。为什么说“相对靠谱”呢?因为我发现它某些情况下也会没有返回所有依赖的，这应该是SQL Server的bug吧?如果要把所有依赖都找回来，你可以去修改被遗忘的引用存储过程，随便加个空行，运行(就是保存结果)，你会发现之前没有显示的依赖终于出现了。而且，sp_depends会输出重复的记录。。。所以我们在代码中要剔除掉。 3. 既然是输出到EXCEL文件，我们就需要找相应的代码。在这个网站已经有很多EXCEL文件生成的代码了，譬如NPOI。我最后采用了GemBox的，因为够轻便。本来想用更轻便的MyXLS，但发现它不支持单背景色。当然你也可以用别的，譬如XML格式的EXCEL文件，这是你个人的选择了。 解决了上述的2个问题，我们就可以大干一场了。我还是采用VS2005+C#2.0，因为公司还是在用古老的XP搭配VS2005，鬼佬国家要求什么都正版，自然不会像我们在中国那样随便就升级到2010了。所以只能放弃LINQ，老老实实地写冗余的代码了。 以下代码没有什么特别的，都是循环所有存储过程，然后循环每个存储过程的依赖对象，然后排序输出(先按照类型，然后按照名称)。代码也写得很quick and dirty，10来分钟的事情，不要跟代码规范较真。 using System; using System.Collections.Generic; using System.Text; using System.IO; using System.Drawing; using System.Data; using System.Data.SqlClient; using GemBox.Spreadsheet; namespace SQLServerDocumenter { class Program { static void Main(string[] args) { if (args.Length == 0) { args = new string[3]; args[0] = "FooDB"; args[1] = "FooServer"; args[2] = "FooPassword"; } string db = args[0]; string dataSource = args.Length > 1 ? args[1] : string.Empty; string password = args.Length > 2 ? args[2] : string.Empty; ExcelFile xls = new ExcelFile(); ExcelWorksheet sheet = xls.Worksheets.Add("Dictionary"); CellStyle typeStyle = new CellStyle(xls); typeStyle.FillPattern.SetSolid(Color.Yellow); typeStyle.Font.Color = Color.Black; typeStyle.Font.Weight = ExcelFont.BoldWeight; CellStyle nameStyle = new CellStyle(xls); nameStyle.FillPattern.SetSolid(Color.DarkGray); nameStyle.Font.Color = Color.Black; nameStyle.Font.Weight = ExcelFont.BoldWeight; CellStyle itemStyle = new CellStyle(xls); itemStyle.FillPattern.SetSolid(Color.LightGray); itemStyle.Font.Color = Color.Black;
itemStyle.Font.Weight = ExcelFont.BoldWeight; sheet.Cells[0, 0].Value = string.Format("{0} database dictionary", db); sheet.Cells[4, 0].Value = "Name"; sheet.Cells[4, 0].Style = nameStyle; sheet.Cells[4, 1].Value = "Dependencies"; sheet.Cells[4, 1].Style = nameStyle; sheet.Cells[4, 2].Value = "Type"; sheet.Cells[4, 2].Style = nameStyle; string connectionString = string.Format("Password={0};Persist Security Info=True;User ID=sa;Initial Catalog={1};Data Source={2}", password, db, dataSource); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); sheet.Cells[5, 0].Value = "Stored Procedures"; sheet.Cells[5, 0].Style = typeStyle; DataSet data = new DataSet(); using (SqlCommand command = new SqlCommand("SELECT * FROM sysobjects WHERE XTYPE='p' ORDER BY NAME", connection)) { SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(data); DataTable objects = data.Tables[0]; int index = 6; for (int i = 0; i < objects.Rows.Count; i++) { string objectName = objects.Rows[i]["name"].ToString(); sheet.Cells[index, 0].Value = objectName; sheet.Cells[index, 0].Style = itemStyle; DataSet data2 = new DataSet(); using (SqlCommand command2 = new SqlCommand(string.Format("exec sp_depends '{0}'", objectName), connection)) { adapter = new SqlDataAdapter(command2); adapter.Fill(data2); } if (data2.Tables.Count > 0) { DataTable dependencies = data2.Tables[0]; Dictionary> uniqueDependencies = new Dictionary>(); for (int j = 0; j < dependencies.Rows.Count; j++) { string itemName = dependencies.Rows[j]["name"].ToString(); if (!uniqueDependencies.ContainsKey(itemName)) uniqueDependencies.Add(itemName, new KeyValuePair(itemName, dependencies.Rows[j]["type"].ToString())); } List> allItems = new List>(); foreach (KeyValuePair> item in uniqueDependencies) { allItems.Add(new KeyValuePair(item.Value.Key, item.Value.Value)); } allItems.Sort(new KVPComparer()); foreach (KeyValuePair item in allItems) { index++; sheet.Cells[index, 1].Value = item.Key; sheet.Cells[index, 2].Value = item.Value; } } else { index++; sheet.Cells[index, 1].Value = "(N/A)"; } index += 3; Console.WriteLine(string.Format("({0}/{1}) {2} done", i + 1, objects.Rows.Count, objectName)); } } connection.Close(); } string path = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\" + db + ".xls"; xls.SaveXls(path); Console.WriteLine(); Console.WriteLine("all done!"); Console.Read(); } } internal class KVPComparer : IComparer> { public int Compare(KeyValuePair x, KeyValuePair y) { int compare = string.Compare(x.Value, y.Value); if (compare == 0) return string.Compare(x.Key, y.Key); else return compare; } } } 使用 使用很简单，编译(你得找个EXCEL输出代码。。。)，在命令行(改成Win应用也可以啊)输入3个参数：数据库名、服务器名和密码。当然，大家都有自己的品味，喜欢怎么改输出格式就怎么改吧。 结论 印度资深系统分析员只是让我给个EXCEL文件，没有让我写代码，所以把我自己的研究成果发上来也无伤大雅。一般我都喜欢把写的东西弄成可重用的，不仅仅为了一个固定的目的，所以也便有了3个参数。 最后输出的的EXCEL文件有6000多行，我真怀疑到底有多少人愿意看这个文件。。。 题外话 其实漂洋过海来了澳洲，来到这个都是印度开发人员的公司，经常让我做些工作，最后都不采纳的，或许，印度人跟哪个国家的人都一样，对the new guy表现好的就要让他halt一下。。。 譬如让我用了一个星期研究SSIS，成果都出来了，最后给无视了。所以，也便有了 数据处理利器-SSIS入门与进阶 这篇文章，省得让我的研究给扔到大海。 另外一个题外话：同事给报表执行一个复杂的SQL查询(存储过程)，以前都是在几秒内完成的，某天开始，要4分钟，怎么改都是要4分钟，任何机器都是，但在数据库本身所在的SSMS跑却正常。后来在业务执行插入SET ARITHABORT ON，问题解决。最后发现是SQL Plan出了问题，只需要修改一下存储过程(随便加个空行)，保存便可，不需要SET ARITHABORT ON。