自動輸出SQL Server對象依賴列表到EXCEL文件
2010-07-12 14:25 靈感之源 閱讀(5324) 評論(13) 收藏 舉報前言
類似的軟件很多年前寫過，不過現在在新國家，新環境，印度佬（我囧）資深系統分析員要求我：給現有的數據庫的所有存儲過程分別列舉所有依賴的對象。
需求
現在數據庫很老很大，表不多，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...
浙公網安備 33010602011771號