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

baacloud免费翻墙vpn注册使用

[转载]自动输出SQL Server存储过程依赖列表到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
<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。

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏