Dependencies between SQL objects: using regular expressions and a small algorithmic focus

Introduction

Databases came to the world in the year 70. Since then, they have become larger in size, with more complex logic and continue to grow. Since then, many tools have been developed for parsing SQL and building dependencies. They all break SQL into tokens using one or another parser and grammar.

But ordinary parsers (ANTLR, for example) may encounter script parsing errors, each SQL dialect has its own peculiarities. Analysis time can also be significant on large databases.

I want to show a much simpler way: RegEx + a little trick,
so ...

The main "chip"

1. Get single-file SQL script for all db objects
2. Split an SQL script into text blocks using a regular expression that matches DDL statements
3. Search for some text string among these text blocks
4. ...
5. PROFIT !!! 1

Implementation or how it works

1. Create a script for all SQL objects. Single file

Each RDBMS has the ability to generate SQL scripts drop / create. So, first we get a single SQL script for all database objects. Or we can take northwind.sql as an example.

2. Find all drop / create / alter DDL in the script using regular expressions

I use this expression

\b(create|alter)\s+(unique)*\s*(nonclustered)*\s*(procedure|proc|table|trigger|view|function|index)\b\s([^=<>\s\']+).([^=<>\s\'\(]+)[*\s\b\t]*

FastColoredTextBox provides great regex tools, so we use

var result = range.GetRangesByLines(regexStr, RegexOptions.IgnoreCase);

The core of this software is actually the following lines of code:

var range = Syncronized(() => fastColoredTextBox1.Selection.Clone());
range.Normalize();
range.Start = new Place(0, 0);
range.End = Syncronized(() => new Place(tb.GetLineLength(tb.LinesCount - 1), tb.LinesCount - 1));

i.e., breaking the SQL file into ranges (Range).

We just load the SQL script into FastColoredTextBox (thanks to the authors!) And apply some regular expression to all its contents. As a result, we got the RESULT variable, which contains a list of the ranges found.

Range is a powerful class (thanks again to the author of FastColoredTextBox). The range contains the row number and column number of the found fragment in the SQL script. We just save the list of ranges in a virtual list (general method), and on SelectedIndexChanged we do the following

fastColoredTextBox1.Selection = range;
fastColoredTextBox1.DoSelectionVisible();

This provides us with convenient and quick navigation through the SQL file.

3. Find all occurrences of the SQL object (or arbitrary string) in the file.

We create another list of ranges (Range class from the FastColoredTextBox library), but instead of the regular expression string we put the name of some SQL object

4. The intersection of two lists of ranges - the same algorithmic focus.

The last step is to find occurrences from the second list of ranges in the first. This is done in the method.

private void FindUsage(string regex)

see class Form1.cs

That's all, the rest of the work is just plain .NET coding to put all the components together.

Important Notice

Since this method of analyzing SQL dependencies is not based on metadata, you should always remember that you are combining two sets of data from strings. Thus, there are some restrictions or, better to say, “features” :)

Let's say we have a stored procedure

create proc test
as
declare @somevar int
create table #tmp(
colum_we_search nvarchar(255),
somevar int)

If we use the regex instruction that matches “CREATE TABLE”, we will get the #TMP table in the results of the match along with CREATE PROC.

Then, if we try to find "COLUMN_WE_SEARCH" - it will be found inside this temporary table #TMP, and not in the TEST procedure.

This can be beaten using a more precise initial regular expression. For such cases, I wrote the regex operator DdlObjectsPreparedProcViewTrigger (see above) - matching DDL statements only for proc, views, triggers and functions, i.e. only for SQL objects containing code.

Miscellaneous and Tasty

In the future, I would like to add MSAGL support for visualizing dependencies and for greater visibility!

FastColoredTextBox is a hi-end, full breakthrough! I did not expect that it has so many interesting features!

→ Sources
→ Compiled version (archive with exe file)