今天给大家分享我自己编写的数据库表结构文档生成工具，方便大家在实际开发当中，可以很方便导出业务系统的表结构，也可以作为项目验收文档中数据库设计文档使用。这样可以大大减少编写数据库表结构文档的时间，有需要的朋友欢迎下载或者沟通交流！

●编程语言：C# （ Net Framework4.5.5）

●数据库技术框架：Dapper

【资料图】

●导出Word文档：NPOI

●访问方式：WinForm窗体应用程序，Windows操作系统可以直接运行

●支持SQLServer、MySQL（5.7、8.0）、SQLite 三种类型的数据，持续更新

●支持Word、Html、MarkDown 三种格式的导出

●导出内容包含数据表（字段详情、字段注释、长度、默认值等）、创建表脚本、视图及视图脚本、存储过程及脚本

●支持生成文档的同时直接打开文档

●支持数据库备份（目前只支持SQLServer导出bak备份文件）

/// 
        /// 获取数据库字符串        /// 
        ///         ///         ///         ///         ///         public string GetConnectioning(string servername, string uid, string pwd, string db, string port)        {            return string.Format("server={0};uid={1};pwd={2};database={3}", servername, uid, pwd, db);        }        /// 
        /// 获取数据库列表        /// 
        ///         ///         public List GetDBNameList(string conStr)        {            //List list =new List();            string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] ";            try            {                using (SqlConnection connection = new SqlConnection(conStr))                {                    var list = connection.Query(sql).ToList();                    return list;                }            }            catch            {                return null;            }        }        public List GetDBList(string conStr)        {            //List list =new List();            string sql = "select [name] from master.dbo.sysdatabases where DBId>6 Order By [Name] ";            try            {                using (SqlConnection connection = new SqlConnection(conStr))                {                    var list = connection.Query(sql).ToList();                    return list;                }            }            catch            {                return null;            }        }        /// 
        /// 获取特定数据库的表名列表        /// 
        ///         ///         public List GetDBTableList(string conStr, string dbName = "")        {            var list = new List();            //string sql = "SELECT TABLE_NAME as name FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE="BASE TABLE" ";            string sql = "select a.name AS tableName,CONVERT(NVARCHAR(100),isnull(g.[value],"")) AS tableDesc from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)";            try            {                using (SqlConnection connection = new SqlConnection(conStr))                {                    list = connection.Query(sql).ToList();                }            }            catch            {            }            return list;        }        /// 
        /// 获取特定数据库里面的存储过程        /// 
        ///         ///         ///         public List GetProcList(string conStr, string dbName = "")        {            var list = new List();            string sql = @"  select name as procName, (select text from syscomments where id=OBJECT_ID(name)) as proDerails                         from dbo.sysobjects  o  where OBJECTPROPERTY(id, N"IsProcedure") = 1 order by name  ";            try            {                               using (SqlConnection connection = new SqlConnection(conStr))                {                    list = connection.Query(sql).ToList();                }            }            catch            {            }            return list;        }        /// 
        /// 获取特定数据库里面的视图        /// 
        ///         ///         ///         public List GetViewList(string conStr, string dbName = "")        {            var list = new List();            string sql = @"  select name as viewName, (select text from syscomments where id=OBJECT_ID(name)) as viewDerails                         from dbo.sysobjects  o  where OBJECTPROPERTY(id, N"IsView") = 1 order by name  ";            try            {                              using (SqlConnection connection = new SqlConnection(conStr))                {                    list = connection.Query(sql).ToList();                }            }            catch            {            }            return list;        }        /// 
        /// 获取字段的信息        /// 
        ///         ///         ///         public List GetTableDetail(string tableName, string conStr, string dbName = "")        {            var list = new List();            StringBuilder sb = new StringBuilder();            sb.Append("SELECT [index] = a.colorder,    Title = a.name,    isMark =        CASE    WHEN COLUMNPROPERTY(a.id, a.name, "IsIdentity") = 1 THEN "1" ELSE "0" END, ");            sb.Append("isPK =  CASE   WHEN EXISTS(SELECT  1  FROM sysobjects WHERE xtype = "PK" AND parent_obj = a.id AND name IN(SELECT name  FROM sysindexes WHERE indid IN(SELECT indid  FROM sysindexkeys  WHERE id = a.id AND colid = a.colid)) ) THEN "1" ELSE "0" END, ");            sb.Append(" FieldType = b.name,fieldLenth = COLUMNPROPERTY(a.id, a.name, "PRECISION"),isAllowEmpty =  CASE   WHEN a.isnullable = 1 THEN "1" ELSE "0" END, defaultValue = ISNULL(e.text, ""), fieldDesc = ISNULL(g.[value], "") ");            sb.Append("FROM syscolumns a LEFT JOIN systypes b  ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = "U" AND d.name <> "dtproperties" LEFT JOIN syscomments e ON a.cdefault = e.id ");            sb.Append("LEFT JOIN sys.extended_properties g ON a.id = G.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0");            //--如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息            sb.Append("WHERE d.name = "" + tableName + "" ORDER BY a.id, a.colorder, d.name");            try            {                using (SqlConnection connection = new SqlConnection(conStr))                {                    list = connection.Query(sb.ToString()).ToList();                }            }            catch            { }            return list;        }

/// 
        /// 生成html文件        /// 
        ///         ///         ///         ///         public void CreateToHtml(List list, string conStr, string db, int type, List checkList)        {            StringBuilder sb = new StringBuilder();            sb.Append("");            sb.Append("");            sb.Append("");            sb.Append("
" + db + "数据库说明文档
");            GetDBService(type);            #region 创建一个表格            if (checkList.Where(m => m.Equals("表")).Count() > 0)            {                sb.Append("
一、表结构
");                sb.Append("");                sb.Append("");                if (list.Count > 0)                {                    foreach (var item in list)                    {                        if (item.tableDesc != null && item.tableDesc != "")                        {                            sb.Append("
表名:" + item.tableName + "(" + item.tableDesc + ")
");                        }                        else                        {                            sb.Append("
表名:" + item.tableName + "
");                        }                        sb.Append(" ");                        sb.Append("");                        sb.Append("");                        //从第二行开始 因为第一行是表头                        int i = 1;                        var tabledetaillist = service.GetTableDetail(item.tableName, conStr, db);                        if (tabledetaillist != null && tabledetaillist.Count > 0)                        {                            foreach (var itm in tabledetaillist)                            {                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                sb.Append("");                                i++;                            }                        }                        sb.Append("
 
序号
字段名称
标识
主键
字段类型
字段长度
允许空值
字段默认值
字段备注
" + itm.index + "
" + itm.Title + "
" + itm.isMark + "
" + itm.isPK + "
" + itm.FieldType + "
" + itm.fieldLenth + "
" + itm.isAllowEmpty + "
" + itm.defaultValue + "
" + itm.fieldDesc + "
");                        sb.Append("
" + item.tableName + "建表脚本

");                        sb.Append("" + service.GetTableSQL(item.tableName, conStr) + "");                    }                }            }            #endregion            #region 存储过程            if (checkList.Where(m => m.Equals("存储过程")).Count() > 0)            {                List proclist = new List();                proclist = service.GetProcList(conStr, db);                sb.Append("
二、存储过程
");                if (proclist != null && proclist.Count > 0)                {                    foreach (var item in proclist)                    {                        sb.Append("
存储过程名称：" + item.procName + "
");                        sb.Append("" + item.proDerails + "");                    }                }            }            #endregion            #region 视图            if (checkList.Where(m => m.Equals("视图")).Count() > 0)            {                List viewlist = new List();                viewlist = service.GetViewList(conStr, db);                sb.Append("
三、视图
");                if (viewlist.Count > 0)                {                    foreach (var item in viewlist)                    {                        sb.Append("
视图名称：" + item.viewName + "
");                        sb.Append("" + item.viewDerails + "");                    }                }            }            #endregion            sb.Append("");            sb.ToString();            string filename = db + "-数据库说明文档";//文件名            SaveFileDialog saveDialog = new SaveFileDialog();            saveDialog.DefaultExt = "html";            saveDialog.Filter = "html文件|*.html";            saveDialog.FileName = filename;            saveDialog.ShowDialog();            filename = saveDialog.FileName;            if (filename.IndexOf(":") < 0) return; //被点了取消                     StreamWriter sw1 = new StreamWriter(saveDialog.FileName, false);            sw1.WriteLine(sb);            sw1.Close();            System.Diagnostics.Process.Start(filename);        }

应用程序主界面

支持三种生成文档类型：每次只能选择一种，推荐使用markdown格式

Word文档生成效果

Html文档生成效果

MarkDown文档效果

针对SQLServer数据库备份

GitHub：​​https://github.com/hgmsq/SqlToDocTool​​

Gitee：​​https://gitee.com/hgm1989/SqlToDocTool​​

Gitcode：​​https://gitcode.net/xishining/SqlToDocTool​​​

关键词：