各位博友大家好
先前一段时间接手一个项目，我负责后台需求的接口开发。
但是客户只提供了测试数据库服务器和原型设计，缺少相关文档，就接口开发的话，我相对而言要确定的有两点：一个是需求确认、另一个就是数据库表的确认了。需求可以在原型上体现，但是数据库表关系的话只是开数据库表不是很直观，所以参照之前公司的表结构模板写了一个导出表结构的小程序，现在和大家分享一下。
目前支持sqlserver2012 导出序列、函数、存储过程、表结构
mysql暂时支持导出表结构
Demo源代码下载地址：
代码上不好的地方还望同仁们指教指教，有什么不明白的也可以直接留言问我的
创建测试库、表、存储过程、函数、序列(sqlserver2012以上)，这个我就不说明了，大家自建一个就好，如在这步有问题，请参照有关知识点进行创建，谢谢。
sqlserver2012 – 序列
/// <summary>
/// 取得所有序列
/// </summary>
public static DataTable GetAllSequences(out string strDBName)
{
DataTable dtResult = new DataTable();
DBHelper dbHelper = new DBHelper();
StringBuilder strSql = new StringBuilder("");
strSql.AppendFormat("SELECT 'IF NOT EXISTS(SELECT * FROM sys.sequences WHERE name=N'''+NAME+''') {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" CREATE SEQUENCE '+NAME+' AS bigint {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" START WITH '+CONVERT(VARCHAR,START_VALUE)+'{0}", CommConst.Const_NewLine);
strSql.AppendFormat(" INCREMENT BY '+CONVERT(VARCHAR,INCREMENT)+'; {0}", CommConst.Const_NewLine);
strSql.AppendFormat("GO {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ' AS SEQ_SCRIPT {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" FROM sys.sequences {0}", CommConst.Const_NewLine);
dtResult = dbHelper.GetDataTable(strSql.ToString(), out strDBName);
return dtResult;
}然后将到查询到的结果导出到文本上，关键代码如下：
// 循环输出存储过程
foreach (DataRow drSeq in dtSeqTemp.Rows)
{
strSeqScriptTemp = CommUtility.ObjToString(drSeq["SEQ_SCRIPT"]);
strWriteTemp.Clear();
strWriteTemp.AppendFormat("{0} {1}", strSeqScriptTemp, CommConst.Const_NewLine);
FileHelper.WriteInfo(strWriteFilePath, CommUtility.ObjToString(strWriteTemp));
}sqlserver–函数
/// <summary>
/// 取得所有函数
/// </summary>
public static DataTable GetAllFunction(out string strDBName)
{
DataTable dtResult = new DataTable();
DBHelper dbHelper = new DBHelper();
StringBuilder strSql = new StringBuilder("");
strSql.AppendFormat("SELECT {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" sysobjects.name AS NAME {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,syscomments.text AS SCRIPT {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" FROM sysobjects {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" INNER JOIN syscomments {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ON sysobjects.id=syscomments.id {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" WHERE 1=1 {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" AND sysobjects.[type] in ('TF','FN','IF') {0}", CommConst.Const_NewLine);
dtResult = dbHelper.GetDataTable(strSql.ToString(), out strDBName);
return dtResult;
}然后将到查询到的结果导出到文本上，关键代码如下：
// 循环输出函数
foreach (DataRow drFun in dtFunctionTemp.Rows)
{
strFunctionNameTemp = CommUtility.ObjToString(drFun["NAME"]);
strFunctionDefinitionTemp = CommUtility.ObjToString(drFun["SCRIPT"]).Replace("'", "''");
strWriteTemp.Clear();
strWriteTemp.AppendFormat("/****** Object: UserDefinedFunction [dbo].[{0}] Script Date: {1} ******/ {2}", strFunctionNameTemp, dateNow.ToString("yyyy/MM/dd HH:mm:ss"), CommConst.Const_NewLine);
strWriteTemp.AppendFormat("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) {1}", strFunctionNameTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("DROP FUNCTION [dbo].[{0}] {1}", strFunctionNameTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("SET ANSI_NULLS ON {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("SET QUOTED_IDENTIFIER ON {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) {1}", strFunctionNameTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("BEGIN {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("EXEC dbo.sp_executesql @statement = N' ");
strWriteTemp.AppendFormat("{0} {1}'{1}", strFunctionDefinitionTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("END {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
FileHelper.WriteInfo(strWriteFilePath, CommUtility.ObjToString(strWriteTemp));
}sqlserver–存储过程
/// <summary>
/// 取得所有存储过程
/// </summary>
public static DataTable GetAllProcedure(out string strDBName)
{
DataTable dtResult = new DataTable();
DBHelper dbHelper = new DBHelper();
StringBuilder strSql = new StringBuilder("");
strSql.AppendFormat("SELECT SYSO.name, {0}", CommConst.Const_NewLine); // 存储过程名称
strSql.AppendFormat(" SYSSM.[definition] {0}", CommConst.Const_NewLine); //存储过程
strSql.AppendFormat(" FROM sys.all_objects AS SYSO {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" INNER JOIN sys.sql_modules AS SYSSM {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ON SYSO.object_id=SYSSM.object_id {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" WHERE 1=1 {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" AND SYSO.is_ms_shipped=0 {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" AND SYSO.[type] in ('P') {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ORDER BY SYSO.[name] ASC {0}", CommConst.Const_NewLine);
dtResult = dbHelper.GetDataTable(strSql.ToString(), out strDBName);
return dtResult;
}然后将到查询到的结果导出到文本上，关键代码如下：
// 循环输出存储过程
foreach (DataRow drPro in dtProTemp.Rows)
{
strProNameTemp = CommUtility.ObjToString(drPro["name"]);
strProDefinitionTemp = CommUtility.ObjToString(drPro["definition"]).Replace("'", "''");
strWriteTemp.Clear();
strWriteTemp.AppendFormat("/****** Object: StoredProcedure [dbo].[{0}] Script Date: {1} ******/ {2}", strProNameTemp, dateNow.ToString("yyyy/MM/dd HH:mm:ss"), CommConst.Const_NewLine);
strWriteTemp.AppendFormat("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) {1}", strProNameTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("DROP PROCEDURE [dbo].[{0}] {1}", strProNameTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("SET ANSI_NULLS ON {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("SET QUOTED_IDENTIFIER ON {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) {1}", strProNameTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("BEGIN {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("EXEC dbo.sp_executesql @statement = N' ");
strWriteTemp.AppendFormat("{0} {1}'{1}", strProDefinitionTemp, CommConst.Const_NewLine);
strWriteTemp.AppendFormat("END {0}", CommConst.Const_NewLine);
strWriteTemp.AppendFormat("GO {0}", CommConst.Const_NewLine);
FileHelper.WriteInfo(strWriteFilePath, CommUtility.ObjToString(strWriteTemp));
}sqlserver、mysql – 表结构
/// <summary>
/// 取得所有表结构
/// </summary>
public static DataTable GetAllDBTableScript(out string strDBName)
{
DataTable dtResult = new DataTable();
DBHelper dbHelper = new DBHelper();
StringBuilder strSql = new StringBuilder("");
#region mysql查询表结构
if (AppConfig.DBType == EnumeDBType.mysql.ToString())
{
strSql.AppendFormat(" SELECT {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" DB_Column.table_name AS '表名' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Table.table_comment AS '表说明' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.ORDINAL_POSITION AS '字段序号' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.COLUMN_NAME AS '字段名' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.COLUMN_COMMENT AS '字段说明' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,'' AS '标识' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,(CASE WHEN DB_Column.COLUMN_KEY ='PRI' THEN '√' ELSE '' END) AS '主键' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.DATA_TYPE AS '类型' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.CHARACTER_OCTET_LENGTH AS '占用字节数' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,(CASE WHEN DB_Column.CHARACTER_MAXIMUM_LENGTH IS NULL THEN DB_Column.NUMERIC_PRECISION ELSE DB_Column.CHARACTER_MAXIMUM_LENGTH END )AS '长度' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.NUMERIC_SCALE AS '小数位数' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,(CASE WHEN DB_Column.IS_NULLABLE = 'YES' THEN '√' ELSE '' END ) AS '允许空' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ,DB_Column.COLUMN_DEFAULT AS '默认值' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" FROM information_schema.COLUMNS AS DB_Column {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" LEFT JOIN information_schema.tables AS DB_Table {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" ON DB_Column.table_name=DB_Table.table_name {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" WHERE 1=1 {0}", CommConst.Const_NewLine);
// strSql.AppendFormat(" AND DB_Column.table_schema = 'bianlike' {0}", CommConst.Const_NewLine);
}
#endregion
#region sqlserver查询表结构
if (AppConfig.DBType == EnumeDBType.sqlserver.ToString())
{
strSql.AppendFormat("SELECT {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 表名 = Case When A.colorder=1 Then D.name Else '' End, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 表说明 = Case When A.colorder=1 Then isnull(F.value,'') Else '' End, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 字段序号 = A.colorder, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 字段名 = A.name, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 字段说明 = isnull(G.[value],''), {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 标识 = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in ( {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 类型 = B.name, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 占用字节数 = A.Length, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 长度 = COLUMNPROPERTY(A.id,A.name,'PRECISION'), {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0), {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 允许空 = Case When A.isnullable=1 Then '√'Else '' End, {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" 默认值 = isnull(E.Text,'') {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" FROM syscolumns A {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" Left Join systypes B {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" On A.xusertype=B.xusertype {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" Inner Join sysobjects D {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" On A.id=D.id and D.xtype='U' and D.name<>'dtproperties' {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" Left Join syscomments E {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" on A.cdefault=E.id {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" Left Join sys.extended_properties G {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" on A.id=G.major_id and A.colid=G.minor_id {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" Left Join sys.extended_properties F {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" On D.id=F.major_id and F.minor_id=0 {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" --where d.name='OrderInfo' --如果只查询指定表,加上此条件 {0}", CommConst.Const_NewLine);
strSql.AppendFormat(" Order By A.id,A.colorder {0}", CommConst.Const_NewLine);
}
#endregion
dtResult = dbHelper.GetDataTable(strSql.ToString(), out strDBName,true);
return dtResult;
}然后将到查询到的结果导出到Excel上（使用的ClosedXML导出Excel），关键代码如下：
// 把模板文件复制到要导出的Excel文件中
File.Copy(strExcelTempFilePath, strExportExcelFilePath, true);
using (ExcelUtility excel = new ExcelUtility(strExportExcelFilePath))
{
// 打开要导出的Excel文件
excel.OpenExcelFile();
// 表名称
//string strDBTableNameTemp = $"{intTableIndex}_{CommUtility.ObjToString(dtTemp.Rows[0]["表名"])}";
string strDBTableNameTemp = CommUtility.ObjToString(dtTemp.Rows[0]["表名"]);
string strDBTableDescTemp = CommUtility.ObjToString(dtTemp.Rows[0]["表说明"]);
// 完整名称（索引_表名）
string strFullDBTableNameString = $"{intTableIndex}_{CommUtility.ObjToString(dtTemp.Rows[0]["表名"])}";
// 截取后的表名称（最大长度31位）
string strCutDBTableNameString = strFullDBTableNameString;
if (strCutDBTableNameString.Length >= 30)
{
strCutDBTableNameString = strCutDBTableNameString.Substring(0, 31);
}
// 循环查询的表信息
foreach (DataRow drTemp in dtTemp.Rows)
{
// 如果当前行表名字段不为空，并且不等于上一个表名
if (intTalbeListRowIndex == 3
|| (CommUtility.ObjToString(drTemp["表名"]) != ""
&& CommUtility.ObjToString(drTemp["表名"]) != strDBTableNameTemp))
{
strDBTableNameTemp = CommUtility.ObjToString(drTemp["表名"]);
strDBTableDescTemp = CommUtility.ObjToString(drTemp["表说明"]);
// 完整名称（索引_表名）
strFullDBTableNameString = $"{intTableIndex}_{CommUtility.ObjToString(drTemp["表名"])}";
// 截取后的表名称（最大长度31位）
strCutDBTableNameString = strFullDBTableNameString;
if (strCutDBTableNameString.Length >= 31)
{
strCutDBTableNameString = strCutDBTableNameString.Substring(0, 31);
}
// 切换Excel的sheet到表一览sheet
excel.SelectActiveSheet("表一览");
//追加一行
excel.RowInsert(intTalbeListRowIndex + 1, 1);
// 写上当前表名、表描述
excel.SetValue("B" + intTalbeListRowIndex, "'" + strFullDBTableNameString); // 表名
excel.SetValue("C" + intTalbeListRowIndex, "'" + strDBTableDescTemp); // 表说明
// 判断导出的Excel中指定sheet是否存在，不存在创建
intSheetExistTemp = excel.IsSheetExsists(strDBTableNameTemp);
if (intSheetExistTemp < 0)
{
// 添加sheet，将表模板sheet复制到新创建的sheet中
excel.AddNewSheet(strCutDBTableNameString, strCopyTableSheetName);
// 添加链接
excel.AddHyperLink("B" + intTalbeListRowIndex, strCutDBTableNameString, "C3");
// 切换到新创建的Sheet上
excel.SelectActiveSheet(strCutDBTableNameString);
intTalbeScriptRowIndex = 7;
}
// sheet表一览行号加1
intTalbeListRowIndex++;
intTableIndex++;
}
excel.SetValue("C3", "'" + strFullDBTableNameString); // 表名
excel.SetValue("C4", "'" + strDBTableDescTemp); // 表说明
//追加一行
excel.RowInsert(intTalbeScriptRowIndex + 1, 1);
excel.SetValue("B" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["字段序号"])); // 字段序号
excel.SetValue("C" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["字段名"])); // 字段名
excel.SetValue("D" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["字段说明"])); // 字段说明
excel.SetValue("E" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["标识"])); // 标识
excel.SetValue("F" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["主键"])); // 主键
excel.SetValue("G" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["类型"])); // 类型
excel.SetValue("H" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["占用字节数"])); // 占用字节数
excel.SetValue("I" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["长度"])); // 长度
excel.SetValue("J" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["小数位数"])); // 小数位数
excel.SetValue("K" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["允许空"])); // 允许空
excel.SetValue("L" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["默认值"])); // 默认值
intTalbeScriptRowIndex++;
}
excel.DeleteSheet(strCopyTableSheetName);
excel.DeleteSheet(strCopyTableDataSheetName);
excel.SaveFile();
}表结构的效果图如下图：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
