程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 自動輸出SQL Server存儲過程依賴列表到EXCEL文件

自動輸出SQL Server存儲過程依賴列表到EXCEL文件

編輯：關於SqlServer

　　前言

　　類似的軟件很多年前寫過，不過現在在新國家，新環境，印度佬（我囧）資深系統分析員要求我：給現有的數據庫的所有存儲過程分別列舉所有依賴的對象。

　　需求

　　現在數據庫很老很大，表不多，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。