ID #64934

自动输出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;
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...


2011-08-24 19:42
阅读:
I'm VC , Just U know Y
本站部分文章来源于互联网,版权归原作者所有。

延伸阅读:

SQL Server 数据存储与 NTFS 簇的大小

关于连接SQL Server 2000时提示“SQL Server不存在或拒绝访问”的处理

SQL 2005 发送邮件 存储过程

如何管理SQL Server的数据库对象

SQL SERVER 2000 连接不上的解决方法