Export table structure to Excel export stored procedure, sequence, function script (sqlserver, mysql)

Hello, everyone

Previously, I took over a project, and I was responsible for the interface development of backstage requirements.
However, the customer only provides the test database server and prototype design, and lacks relevant documents. In terms of interface development, I have two points to make sure: one is to confirm the requirements, the other is to confirm the good database table. The requirements can be reflected in the prototype, but if the database table relationship is just to open the database table, it is not very intuitive, so refer to the previous company's Table structure template has written a small program of table structure everywhere. Now let's share it with you.
At present, sqlserver2012 is supported to export sequence, function, stored procedure and table structure
mysql temporarily supports export table structure

Download address of Demo source code: it can be downloaded in my resources, but it is still under review. There is no link to provide it. After the review, I will provide it again

Create a test library, table, stored procedure, function and sequence (above sqlserver2012). I won't explain that. Just build one by yourself. If you have problems in this step, please refer to the relevant knowledge points for creation. Thank you.

sqlserver2012 - sequence

        /// <summary>
        ///Get all sequences
        /// </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;
        }

Then export the query results to the text. The key codes are as follows:

            // Cyclic output stored procedure
            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 - function

        /// <summary>
        ///Get all functions
        /// </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;
        }

Then export the query results to the text. The key codes are as follows:

            // Cyclic output function
            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 - stored procedure

        /// <summary>
        ///Get all stored procedures
        /// </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); // Stored procedure name
            strSql.AppendFormat("       SYSSM.[definition] {0}", CommConst.Const_NewLine); //stored procedure
            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;
        }

Then export the query results to the text. The key codes are as follows:

            // Cyclic output stored procedure
            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 - table structure

        /// <summary>
        ///Get all table structures
        /// </summary>
        public static DataTable GetAllDBTableScript(out string strDBName)
        {
            DataTable dtResult = new DataTable();
            DBHelper dbHelper = new DBHelper();
            StringBuilder strSql = new StringBuilder("");

            #region mysql query table structure

            if (AppConfig.DBType == EnumeDBType.mysql.ToString())
            {
                strSql.AppendFormat(" SELECT  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("         DB_Column.table_name AS 'Table name' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Table.table_comment AS 'Table description' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.ORDINAL_POSITION AS 'Field ordinal number' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.COLUMN_NAME AS 'Field name' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.COLUMN_COMMENT AS 'Field description' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,'' AS 'Identification' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,(CASE WHEN DB_Column.COLUMN_KEY ='PRI' THEN '√' ELSE '' END) AS 'Primary key' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.DATA_TYPE AS 'type' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.CHARACTER_OCTET_LENGTH AS 'Bytes occupied' {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 'length' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.NUMERIC_SCALE AS 'Decimal digit' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,(CASE WHEN DB_Column.IS_NULLABLE = 'YES' THEN '√' ELSE '' END ) AS 'Allow space' {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("        ,DB_Column.COLUMN_DEFAULT AS 'Default value'        {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 query table structure

            if (AppConfig.DBType == EnumeDBType.sqlserver.ToString())
            {
                strSql.AppendFormat("SELECT   {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Table name       = Case When A.colorder=1 Then D.name Else '' End,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Table description     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Field ordinal number   = A.colorder,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Field name     = A.name,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Field description   = isnull(G.[value],''),  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Identification       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Primary key       = 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("       type       = B.name,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Bytes occupied = A.Length,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       length       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Decimal digit   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Allow space     = Case When A.isnullable=1 Then '√'Else '' End,  {0}", CommConst.Const_NewLine);
                strSql.AppendFormat("       Default value     = 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'    --If only the specified table is queried,Add this condition  {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;
        }

Then export the query results to excel (ClosedXML used to export Excel). The key codes are as follows:

                // Copy the template file to the Excel file to be exported
                File.Copy(strExcelTempFilePath, strExportExcelFilePath, true);
                using (ExcelUtility excel = new ExcelUtility(strExportExcelFilePath))
                {
                    // Open Excel file to export
                    excel.OpenExcelFile();
                    // Table name
                    //String strdbtablenametemp = $"{inttableindex}" {commutility. Objtostring (dttemp. Rows [0] ["table name"])} ";
                    string strDBTableNameTemp = CommUtility.ObjToString(dtTemp.Rows[0]["Table name"]);
                    string strDBTableDescTemp = CommUtility.ObjToString(dtTemp.Rows[0]["Table description"]);
                    // Full name (index table name)
                    string strFullDBTableNameString = $"{intTableIndex}_{CommUtility.ObjToString(dtTemp.Rows[0]["Table name"])}";
                    // Table name after truncation (maximum length 31 bits)
                    string strCutDBTableNameString = strFullDBTableNameString;
                    if (strCutDBTableNameString.Length >= 30)
                    {
                        strCutDBTableNameString = strCutDBTableNameString.Substring(0, 31);
                    }

                    // Table information of circular query
                    foreach (DataRow drTemp in dtTemp.Rows)
                    {
                        // If the current row table name field is not empty and is not equal to the previous table name
                        if (intTalbeListRowIndex == 3
                            || (CommUtility.ObjToString(drTemp["Table name"]) != ""
                                && CommUtility.ObjToString(drTemp["Table name"]) != strDBTableNameTemp))
                        {
                            strDBTableNameTemp = CommUtility.ObjToString(drTemp["Table name"]);
                            strDBTableDescTemp = CommUtility.ObjToString(drTemp["Table description"]);
                            // Full name (index table name)
                            strFullDBTableNameString = $"{intTableIndex}_{CommUtility.ObjToString(drTemp["Table name"])}";
                            // Table name after truncation (maximum length 31 bits)
                            strCutDBTableNameString = strFullDBTableNameString;
                            if (strCutDBTableNameString.Length >= 31)
                            {
                                strCutDBTableNameString = strCutDBTableNameString.Substring(0, 31);
                            }

                            // Switch Excel sheet to table list sheet
                            excel.SelectActiveSheet("List of tables");
                            //Append one line
                            excel.RowInsert(intTalbeListRowIndex + 1, 1);

                            // Write the name and description of the current table
                            excel.SetValue("B" + intTalbeListRowIndex, "'" + strFullDBTableNameString); // Table name
                            excel.SetValue("C" + intTalbeListRowIndex, "'" + strDBTableDescTemp); // Table description
                            // Determine whether the specified sheet exists in the exported Excel. No creation exists
                            intSheetExistTemp = excel.IsSheetExsists(strDBTableNameTemp);
                            if (intSheetExistTemp < 0)
                            {
                                // Add sheet, copy the sheet template sheet to the newly created sheet
                                excel.AddNewSheet(strCutDBTableNameString, strCopyTableSheetName);
                                // Add links
                                excel.AddHyperLink("B" + intTalbeListRowIndex, strCutDBTableNameString, "C3");
                                // Switch to the newly created Sheet
                                excel.SelectActiveSheet(strCutDBTableNameString);
                                intTalbeScriptRowIndex = 7;
                            }
                            // sheet list line number plus 1
                            intTalbeListRowIndex++;
                            intTableIndex++;
                        }

                        excel.SetValue("C3", "'" + strFullDBTableNameString); // Table name
                        excel.SetValue("C4", "'" + strDBTableDescTemp); // Table description

                        //Append one line
                        excel.RowInsert(intTalbeScriptRowIndex + 1, 1);

                        excel.SetValue("B" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Field ordinal number"])); // Field ordinal number
                        excel.SetValue("C" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Field name"])); // Field name
                        excel.SetValue("D" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Field description"])); // Field description
                        excel.SetValue("E" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Identification"])); // Identification
                        excel.SetValue("F" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Primary key"])); // Primary key
                        excel.SetValue("G" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["type"])); // type
                        excel.SetValue("H" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Bytes occupied"])); // Bytes occupied
                        excel.SetValue("I" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["length"])); // length
                        excel.SetValue("J" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Decimal digit"])); // Decimal digit
                        excel.SetValue("K" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Allow space"])); // Allow space
                        excel.SetValue("L" + intTalbeScriptRowIndex, "'" + CommUtility.ObjToString(drTemp["Default value"])); // Default value
                        intTalbeScriptRowIndex++;
                    }
                    excel.DeleteSheet(strCopyTableSheetName);
                    excel.DeleteSheet(strCopyTableDataSheetName);
                    excel.SaveFile();
                }

The effect diagram of the table structure is as follows:

Tags: Excel Stored Procedure Database MySQL

Posted on Sat, 09 Nov 2019 10:41:57 -0500 by mark110384