Using Microsoft Copilot to Accelerate Complex SQL Code Analysis: A Real Case Study
Analyzing large volumes of SQL code is notoriously challenging because real‑world queries are rarely clean, consistent, or structured in a predictable way. While tools like Microsoft Copilot can significantly accelerate this work, they deliver real value only when guided with the right methodology and domain expertise. Today I would like to share a real‑world example of how Copilot helped me complete a complex data analysis task in just two days—a task that would have taken more than 30 days if done fully manually.
Context: The Foundations of Data Mapping
In this article, I would like to share my first experience using AI to analyze a large volume of SQL code in the context of Data Mapping. Before moving on to the practical part, I will briefly explain what Data Mapping is.
As the name suggests, Data Mapping is the process of aligning or matching data. It is most often used when data needs to be mapped between multiple systems. Imagine a Legacy system that has been developed over many years and whose data is consumed by other applications. Now suppose this Legacy system is being replaced by a new system that fully or partially covers the functionality of the old one. A natural question arises: how can we introduce the new system and decommission the old one in such a way that all dependent systems continue to operate without interruption?
One of the approaches is to create an intermediate layer between the new system and the systems that depend on the Legacy platform. This layer transforms the data and its structure from the new system into the format expected by the old one, allowing dependent systems to continue functioning as before. Implementing such a layer involves Data Mapping — the process of aligning the data model of the new system with the data model of the old one.
In a classical migration project, my role was to identify the objects (tables and fields) in the Legacy system’s database, find their equivalents in the new system, describe the required transformations, and document gaps — scenarios where the new system cannot fully reproduce the data from the old one.
The Problem
My task was to determine which Legacy system objects were used by one of the downstream systems that relied on Legacy data as its primary source for analysis and reporting. This information was essential for identifying which Legacy objects needed to be mapped first to their counterparts in the new system. Unfortunately, no documentation existed for the downstream application. The only available artifact was its SQL code — and analyzing that code with the help of AI became the foundation of my approach.
I was given a list of 78 SQL files, containing more than 12,000 lines of SQL code in total.
The objective was to:
Identify all tables and fields used across the queries
Determine where in each query those tables and fields were used
Calculate how often each table and field appeared across the entire codebase
This type of analysis is typically very time-consuming and error-prone when done manually, especially at this scale.
Initial Copilot Approach and Challenges
Considering Copilot’s limitations with file volume, I merged all 78 SQL files into a single file and asked Copilot to analyze it. I requested the output in Excel format, with the following columns:
Column Name | Description
|
DB Name | Name of the target database |
Schema Name | Database schema name |
Table Name | Name of the identified table |
Field Name | Name of the identified field |
Statement | Type/context of the SQL statement |
Frequency | Occurrence count across the codebase |
Although Copilot produced a result, it was insufficient and inconsistent. The output contained very few tables and fields, and the results did not align with my understanding of the codebase based on prior experience.
I restarted the analysis multiple times using the same prompt, but each attempt produced different and still incomplete results. This inconsistency quickly became one of the biggest challenges. The inability to achieve stable results from the same input and prompt significantly reduced confidence in the output.
After several attempts, I realized that Copilot was trying to treat the SQL code as well-structured text, relying heavily on different regular expression patterns to parse it. However, SQL code-especially large, real-world SQL-is not consistently structured, which led to unreliable parsing and missed objects.
Adjusting the Strategy: Guiding Copilot
At this point, I concluded that I should not rely entirely on Copilot’s default analytical approach. Instead, I needed to guide Copilot toward the correct methodology.
Key observations:
All SQL code was based on a single database.
A complete data dictionary for that database already existed.
I had a data dictionary document, but I decided not to rely on it. Instead, I created an auxiliary reference table containing a complete list of all tables and fields available in the database. Writing a SQL query to extract all database objects and their attributes was straightforward. After generating this list, I provided it to Copilot and updated the task definition accordingly.
Ask Copilot to identify only tables and fields present in the provided dictionary
Use a hybrid approach:
Treat SQL as unstructured text where necessary
Use the data dictionary to validate real table and field usage
Apply regular expressions selectively, only where they made sense
Once Copilot was aligned with the correct analytical approach, the results improved dramatically. The identified tables and fields finally matched my expectations and experience with the codebase.
Key Takeaways
Do not blindly rely on the approach Copilot proposes. Copilot often makes assumptions about structure that may not fit real-world data.
Human expertise is critical. Your domain knowledge is essential to guiding Copilot toward the right solution.
Validate the results using your own knowledge and expectations. If you do not have a well-grounded expectation of what the outcome should look like, do not use Copilot’s output as the primary source for decision-making.
Expect variability and restart analysis when needed. Running the same analysis multiple times can produce different results.
Leverage Copilot’s memory and context. Treat each successful iteration like a “save point” in a computer game. Build forward from what works instead of restarting blindly.
Art of Business Analysis training schedule
News and articles on business analysis:
