需求
現(xiàn)在數(shù)據(jù)庫很老很大，表不多，200來個，但數(shù)據(jù)量很大：的數(shù)據(jù)表2億6千萬條，每天增加50多w，925個存儲過程。
系統(tǒng)大，耦合度很高，牽一發(fā)而動全身。人員變動頻繁，接手的人員要在修改之前，就得花相當(dāng)長的時間來分析關(guān)聯(lián)性。
所以，印度資深系統(tǒng)分析員要求我在一個EXCEL文件中，把925個存儲過程的所有依賴的對象（表、函數(shù)、視圖、存儲過程等等）都列舉出來。
分析
手工逐個打開存儲過程去做，對寫軟件的人來說是很傻的事情，一般重復(fù)性工作，如果預(yù)計耗時超過3分鐘，我就會卷起袖子寫個代碼。
工作內(nèi)容有3部分：
1.獲取所有的存儲過程。我們可以用sysobjects這個系統(tǒng)表，它存儲了所有的表、存儲過程、視圖、函數(shù)等。其中存儲過程的xtype是P。另外說一下，如果是CLR存儲過程，類型是PC。
2. 獲取某存儲過程所依賴的對象，當(dāng)然是先google了。很久之前我就知道可以用系統(tǒng)存儲過程sp_depends來獲取，不過還是應(yīng)該看看還有什么更好的辦法。首先我發(fā)現(xiàn)這個：http://www.mssqltips.com/tip.asp?tip=1294 。作者研究出4種辦法：INFORMATION_SCHEMA.ROUTINES/sp_depends/syscomments/sp_MSdependencies。其中就有我一直在用的sp_depends。其它辦法有的霸王硬上弓：用charindex來遍歷存儲過程內(nèi)容，或者用LIKE來判斷。。。。。我服了，寫代碼的風(fēng)格千差萬別，一些是[Foo]，一些是Foo，而且不同的存儲過程名稱可能存在完全給另外一個包含，譬如Foo Foo1 AFoo等。
看完之后，我還是覺得使用sp_depends相對靠譜。為什么說“相對靠譜”呢？因為我發(fā)現(xiàn)它某些情況下也會沒有返回所有依賴的，這應(yīng)該是SQL Server的bug吧？如果要把所有依賴都找回來，你可以去修改被遺忘的引用存儲過程，隨便加個空行，運(yùn)行（就是保存結(jié)果），你會發(fā)現(xiàn)之前沒有顯示的依賴終于出現(xiàn)了。而且，sp_depends會輸出重復(fù)的記錄。。。所以我們在代碼中要剔除掉。
3. 既然是輸出到EXCEL文件，我們就需要找相應(yīng)的代碼。在這個網(wǎng)站已經(jīng)有很多EXCEL文件生成的代碼了，譬如NPOI。我最后采用了GemBox的，因為夠輕便。本來想用更輕便的MyXLS，但發(fā)現(xiàn)它不支持單背景色。當(dāng)然你也可以用別的，譬如XML格式的EXCEL文件，這是你個人的選擇了。
解決了上述的2個問題，我們就可以大干一場了。我還是采用VS2005+C#2.0，因為公司還是在用古老的XP搭配VS2005，鬼佬國家要求什么都正版，自然不會像我們在中國那樣隨便就升級到2010了。所以只能放棄LINQ，老老實實地寫冗余的代碼了。
以下代碼沒有什么特別的，都是循環(huán)所有存儲過程，然后循環(huán)每個存儲過程的依賴對象，然后排序輸出（先按照類型，然后按照名稱）。代碼也寫得很quick and dirty，10來分鐘的事情，不要跟代碼規(guī)范較真。
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應(yīng)用也可以?。┹斎?個參數(shù)：數(shù)據(jù)庫名、服務(wù)器名和密碼。當(dāng)然，大家都有自己的品味，喜歡怎么改輸出格式就怎么改吧。 結(jié)論 印度資深系統(tǒng)分析員只是讓我給個EXCEL文件，沒有讓我寫代碼，所以把我自己的研究成果發(fā)上來也無傷大雅。一般我都喜歡把寫的東西弄成可重用的，不僅僅為了一個固定的目的，所以也便有了3個參數(shù)。 最后輸出的的EXCEL文件有6000多行，我真懷疑到底有多少人愿意看這個文件。。。 題外話 其實漂洋過海來了澳洲，來到這個都是印度開發(fā)人員的公司，經(jīng)常讓我做些工作，最后都不采納的，或許，印度人跟哪個國家的人都一樣，對the new guy表現(xiàn)好的就要讓他halt一下。。。 譬如讓我用了一個星期研究SSIS，成果都出來了，最后給無視了。所以，也便有了 數(shù)據(jù)處理利器-SSIS入門與進(jìn)階 這篇文章，省得讓我的研究給扔到大海。 另外一個題外話：同事給報表執(zhí)行一個復(fù)雜的SQL查詢（存儲過程），以前都是在幾秒內(nèi)完成的，某天開始，要4分鐘，怎么改都是要4分鐘，任何機(jī)器都是，但在數(shù)據(jù)庫本身所在的SSMS跑卻正常。后來在業(yè)務(wù)執(zhí)行插入SET ARITHABORT ON，問題解決。最后發(fā)現(xiàn)是SQL Plan出了問題，只需要修改一下存儲過程（隨便加個空行），保存便可，不需要SET ARITHABORT ON。
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應(yīng)用也可以?。┹斎?個參數(shù)：數(shù)據(jù)庫名、服務(wù)器名和密碼。當(dāng)然，大家都有自己的品味，喜歡怎么改輸出格式就怎么改吧。 結(jié)論 印度資深系統(tǒng)分析員只是讓我給個EXCEL文件，沒有讓我寫代碼，所以把我自己的研究成果發(fā)上來也無傷大雅。一般我都喜歡把寫的東西弄成可重用的，不僅僅為了一個固定的目的，所以也便有了3個參數(shù)。 最后輸出的的EXCEL文件有6000多行，我真懷疑到底有多少人愿意看這個文件。。。 題外話 其實漂洋過海來了澳洲，來到這個都是印度開發(fā)人員的公司，經(jīng)常讓我做些工作，最后都不采納的，或許，印度人跟哪個國家的人都一樣，對the new guy表現(xiàn)好的就要讓他halt一下。。。 譬如讓我用了一個星期研究SSIS，成果都出來了，最后給無視了。所以，也便有了 數(shù)據(jù)處理利器-SSIS入門與進(jìn)階 這篇文章，省得讓我的研究給扔到大海。 另外一個題外話：同事給報表執(zhí)行一個復(fù)雜的SQL查詢（存儲過程），以前都是在幾秒內(nèi)完成的，某天開始，要4分鐘，怎么改都是要4分鐘，任何機(jī)器都是，但在數(shù)據(jù)庫本身所在的SSMS跑卻正常。后來在業(yè)務(wù)執(zhí)行插入SET ARITHABORT ON，問題解決。最后發(fā)現(xiàn)是SQL Plan出了問題，只需要修改一下存儲過程（隨便加個空行），保存便可，不需要SET ARITHABORT ON。