必胜高考网 > 计算机类 > 计算机等级 > 资讯 >

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

时间: 家辉2 资讯
 为大家更好的备考全国计算机等级考试,现学习啦小编专门准备了计算机三级《数据库技术》考前复习重点:自动输出SQLServer存储过程依赖列表到EXCEL文件,希望可以帮到你!
需求

  手工逐个打开存储过程去做,对写软件的人来说是很傻的事情,一般重复性工作,如果预计耗时超过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多行。

56094