自动输出SQL Server存储过程依赖列表到EXCEL文件

核心提示： 看完之后，我还是觉得使用sp_depends相对靠谱，自动输出SQL Server存储过程依赖列表到EXCEL文件(2)，为什么说“相对靠谱”呢？因为我发现它某些情况下也会没有返回所有依赖的，这应该是SQL Server的bug吧？如果要把所有依赖都找回来，只需要修改一下

看完之后，我还是觉得使用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<string,　KeyValuePair<string,　string>>　uniqueDependencies　=　new　Dictionary<string,　KeyValuePair<string,　string>>();
　　　　　　　　　　　　　　　　　　　　　　　　　　　　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<string,　string>(itemName,　dependencies.Rows[j]["type"].ToString()));
　　　　　　　　　　　　　　　　　　　　　　　　　　　　}
　　　　　　　　　　　　　　　　　　　　　　　　　　　　List<KeyValuePair<string,　string>>　allItems　=　new　List<KeyValuePair<string,　string>>();
　　　　　　　　　　　　　　　　　　　　　　　　　　　　foreach　(KeyValuePair<string,　KeyValuePair<string,　string>>　item　in　uniqueDependencies)
　　　　　　　　　　　　　　　　　　　　　　　　　　　　{
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　allItems.Add(new　KeyValuePair<string,　string>(item.Value.Key,　item.Value.Value));
　　　　　　　　　　　　　　　　　　　　　　　　　　　　}
　　　　　　　　　　　　　　　　　　　　　　　　　　　　allItems.Sort(new　KVPComparer());
　　　　　　　　　　　　　　　　　　　　　　　　　　　　foreach　(KeyValuePair<string,　string>　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<KeyValuePair<string,　string>>
　　　　{
　　　　　　　　public　int　Compare(KeyValuePair<string,　string>　x,　KeyValuePair<string,　string>　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。