本文介绍了一款用于自动分析SQL Server数据库中存储过程依赖项的工具，该工具能够快速生成包含所有存储过程及其依赖对象（如表、函数和视图等）的详细Excel文档。
前言
类似的软件很多年前写过，不过现在在新国家，新环境，印度佬（我囧）资深系统分析员要求我：给现有的数据库的所有存储过程分别列举所有依赖的对象。
需求
现在数据库很老很大，表不多，200来个，但数据量很大：最大的数据表2亿6千万条，每天增加50多w，925个存储过程。
系统大，耦合度很高，牵一发而动全身。人员变动频繁，接手的人员要在修改之前，就得花相当长的时间来分析关联性。
所以，印度资深系统分析员要求我在一个EXCEL文件中，把925个存储过程的所有依赖的对象（表、函数、视图、存储过程等等）都列举出来。
分析
手工逐个打开存储过程去做，对写软件的人来说是很傻的事情，一般重复性工作，如果预计耗时超过3分钟，我就会卷起袖子写个代码。
工作内容有3部分：
1.获取所有的存储过程。我们可以用sysobjects这个系统表，它存储了所有的表、存储过程、视图、函数等。其中存储过程的xtype是P，CLR存储过程，类型是PC；函数的类型是FN/IF或TF，CLR函数类型是FS；视图类型是V；表类型是U。
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文件，这是你个人的选择了。
解决了上述的3个问题，我们就可以大干一场了。我用VS2005+C#2.0，因为公司还是在用古老的XP搭配VS2005，鬼佬国家要求什么都正版，自然不会像我们在中国那样随便就升级到2010了。所以只能放弃LINQ，老老实实地写老派的代码了。
以下代码没有什么特别的，都是循环所有存储过程，然后循环每个存储过程的依赖对象，然后排序输出（先按照类型，然后按照名称）。本来想用DataTable.Select对多个字段排序，但后来发现没效果，也没心思去研究为什么，干脆就改成写一个IComparer。
代码写得很quick and dirty，10来分钟的事情，不要跟代码规范较真。
代码（让你容易找点。。。）
代码
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 [ 4 ];
args[ 0 ] = " database " ;
args[ 1 ] = " datasource " ;
args[ 2 ] = " user " ;
args[ 3 ] = " password " ;
}
string db = args[ 0 ];
string dataSource = args.Length > 1 ? args[ 1 ] : string .Empty;
string user = args.Length > 2 ? args[ 2 ] : string .Empty;
string password = args.Length > 3 ? args[ 3 ] : string .Empty;
Work work = new Work();
work.Progress += new EventHandler < ProgressEventArgs > (OnWorkProgress);
work.Run(db, dataSource, user, password);
Console.WriteLine();
Console.WriteLine( " all done! " );
Console.Read();
}
private static void OnWorkProgress( object sender, ProgressEventArgs e)
{
Console.WriteLine(e.Status);
}
}
public class Work
{
public event EventHandler < ProgressEventArgs > Progress;
public void Run( string Database, string DataSource, string UserName, string Password)
{
ExcelFile xls = new ExcelFile();
ExcelWorksheet sheet = xls.Worksheets.Add( " Dictionary " );
CellStyle nameStyle = new CellStyle(xls);
nameStyle.FillPattern.SetSolid(Color.DarkGray);
nameStyle.Font.Color = Color.Black;
nameStyle.Font.Weight = ExcelFont.BoldWeight;
sheet.Cells[ 0 , 0 ].Value = string .Format( " {0} database dictionary " , Database);
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={1};Initial Catalog={2};Data Source={3} " , Password, UserName, Database, DataSource);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
int index = 5 ;
AddObjects(connection, " 'P' " , " Stored Procedures " , new List < string > ( new string [] { " sp_alterdiagram " , " sp_creatediagram " , " sp_dropdiagram " , " sp_helpdiagramdefinition " , " sp_helpdiagrams " , " sp_renamediagram " , " sp_upgraddiagrams " }), sheet, ref index);
AddObjects(connection, " 'FN','IF','TF' " , " Functions " , new List < string > (), sheet, ref index);
AddObjects(connection, " 'V' " , " Views " , new List < string > (), sheet, ref index);
connection.Close();
}
string path = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @" \ " + Database + " .xls " ;
xls.SaveXls(path);
}
private void AddObjects(SqlConnection Connection, string Types, string Name, List < string > IgnoreNames, ExcelWorksheet Sheet, ref int Index)
{
CellStyle itemStyle = new CellStyle();
itemStyle.FillPattern.SetSolid(Color.LightGray);
itemStyle.Font.Color = Color.Black;
itemStyle.Font.Weight = ExcelFont.BoldWeight;
CellStyle typeStyle = new CellStyle();
typeStyle.FillPattern.SetSolid(Color.Yellow);
typeStyle.Font.Color = Color.Black;
typeStyle.Font.Weight = ExcelFont.BoldWeight;
Sheet.Cells[Index, 0 ].Value = Name;
Sheet.Cells[Index, 0 ].Style = typeStyle;
Index ++ ;
DataSet data = new DataSet();
using (SqlCommand command = new SqlCommand( string .Format( " SELECT * FROM sysobjects WHERE XTYPE IN ({0}) ORDER BY NAME " , Types), Connection))
{
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(data);
if (data.Tables.Count > 0 )
{
DataTable objects = data.Tables[ 0 ];
for ( int i = 0 ; i < objects.Rows.Count; i ++ )
{
string objectName = objects.Rows[i][ " name " ].ToString();
if ( ! IgnoreNames.Contains(objectName))
{
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 (itemName.ToLower().StartsWith( " dbo. " ))
itemName = itemName.Substring( 4 );
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 ;
AddProgress( string .Format( " ({0}/{1}) {2} done " , i + 1 , objects.Rows.Count, objectName));
}
else
AddProgress( string .Format( " ({0}/{1}) {2} ignored " , i + 1 , objects.Rows.Count, objectName));
}
}
else
Sheet.Cells[Index, 0 ].Value = " (N/A) " ;
}
Index ++ ;
}
private void AddProgress( string Status)
{
if (Progress != null )
Progress( this , new ProgressEventArgs(Status));
}
}
public class ProgressEventArgs : EventArgs
{
private string status;
public string Status
{
get { return status; }
set { status = value; }
}
public ProgressEventArgs( string Status)
{
status = Status;
}
}
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文件，没有让我写代码，所以把我自己的研究成果发上来也无伤大雅。一般我都喜欢把写的东西弄成可重用的，不仅仅为了一个固定的目的，所以也便有了4个参数和同时输出函数和视图的依赖列表。
最后输出的的EXCEL文件有6000多行，我真怀疑到底有多少人愿意看这个文件。。。
题外话
其实漂洋过海来了澳洲，来到这个都是印度开发人员的公司，经常让我做些工作，最后都不采纳的，或许，印度人跟哪个国家的人都一样，对the new guy表现好的就要让他halt一下。。。枪打出头鸟，人怕出名猪怕壮，新人在试用期间又要给老板看表现，但又不能让老员工有压力，混口饭吃不容易。
譬如让我用了一个星期研究SSIS，成果都出来了，最后给无视了。所以，也便有了 数据处理利器-SSIS入门与进阶 这篇文章，省得让我的研究给扔到大海。
譬如让我研究给那个巨大的数据表分区，我辛苦写了详细的计划，步骤，相关的SQL，注意事项等等等，最后我问起来，一句话答复：我不会应用的。
另外一个题外话：同事给报表执行一个复杂的SQL查询（存储过程），以前都是在几秒内完成的，某天开始，要4分钟，怎么改都是要4分钟，任何机器都是，但在数据库本身所在的SSMS跑却正常。后来在业务执行插入SET ARITHABORT ON，问题解决。最后发现是SQL Plan出了问题，只需要修改一下存储过程（随便加个空行），保存便可，不需要SET ARITHABORT ON。
另外第二个题外话，我发现印度程序员的确能做事的，对要做的事情很熟悉，譬如那个资深系统分析员，对新业务的分析是很快捷准确的。不过写的代码和SQL的规范和质量。。。。，就跟大家平时所说的差不多了
更新
2010-07-13 1.增加了对函数和视图的支持；2.增加了对用户名的支持；3.分离了业务逻辑和界面，现在可以Copy&Paste到WinForm/WPF/WebForm/ASP.NET MVC...
扫一扫
1324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
