@manhnguyenv

Welcome to my blog!

Find duplicate in SQL Server Script using CSharp

February 25, 2020 11:57

Find duplicate in SQL Server Script using CSharp (edit)

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/find-duplicated-variables-in-sql-server-script/

Code SQL

BEGIN TRAN

DECLARE @ProductId BIGINT
DECLARE @OTSProductId BIGINT

SET @ProductId = 35768
SET @OTSProductId = @ProductId

IF NOT EXISTS(SELECT ProductId
              FROM   PRODUCT
              WHERE  PRODUCTID = @ProductId)
  BEGIN
      INSERT INTO [Product]
                  ([ProductId],
                   [Name])
      SELECT @ProductId,
             'Please see the insert in this ....'
  END
ELSE
  BEGIN
      PRINT REPLACE('product  already exists', '', @ProductId)
  END

DECLARE @ProductId BIGINT

SET @ProductId = 35768
SET @OTSProductId = @ProductId

IF NOT EXISTS(SELECT ProductId
              FROM   PRODUCT
              WHERE  PRODUCTID = @ProductId)
  BEGIN
      INSERT INTO [Product]
                  ([ProductId],
                   [Name])
      SELECT @ProductId,
             'Please see the insert in this ....'
  END
ELSE
  BEGIN
      PRINT REPLACE('product  already exists', '', @ProductId)
  END

COMMIT TRAN 

Code CSharp

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;


using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace duplicateVar
{
    class duplicateVar
    {
        static void Main(string[] args)
        {
            string inputfile = "";
            string lcsqltext = "";

            if (args.Length == 0)
            {
                Console.WriteLine("duplicateVar scriptfile");
                return;
            }

            inputfile = args[0];

            //Reading from file
            try
            {
                using (StreamReader sr = new StreamReader(inputfile))
                {
                    //This allows you to do one Read operation.
                    lcsqltext = sr.ReadToEnd();
                }

            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("File could not be read: " + e.Message);
                return;
            }


            TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
            sqlparser.SqlText.Text = lcsqltext;
            int i = sqlparser.Parse();

            if (i == 0) {
                StringBuilder sb = new StringBuilder(1024);
                foreach (TCustomSqlStatement stmt in sqlparser.SqlStatements)
                {
                    if (stmt.SqlStatementType == TSqlStatementType.sstMssqlDeclare)
                    {
                        TMssqlDeclare d = stmt as TMssqlDeclare;
                        if (d.declaretype == 1)
                        {
                            // declare variable
                            _TMssql_declare_var dv;
                            for (int k = 0; k < d.vars.Count(); k++)
                            {
                                dv = d.vars[k] as _TMssql_declare_var;
                                if (sb.ToString().IndexOf(dv._var_name.AsText) >= 0)
                                {
                                    // this variable already declared.
                                    Console.WriteLine("variable duplicated: "+dv._var_name.AsText+" pos("+dv._var_name.XPosition+","+dv._var_name.YPosition+")");
                                }
                                else
                                {
                                    sb.Append(dv._var_name.AsText);
                                    Console.WriteLine("variable declared: " + dv._var_name.AsText + " pos(" + dv._var_name.XPosition + "," + dv._var_name.YPosition + ")");                                    
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                Console.WriteLine(sqlparser.ErrorMessages);
            }
        }
       
    }
}

Categories

Recent posts