Dependencies between SQL objects: use regular expressions and small algorithmic focus

Introduction

Databases came to the world in the year 70. Since then, they have become larger in size, with a more complex logic and continue to grow. Since then, many tools have appeared for parsing SQL and building dependencies. All of them break SQL into tokens using one or another parser and grammars.

But common 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…

Home "chip"

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

Realization or how everything works

1. Create a script of all SQL objects. One file

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

2. Find in the script all drop / create / alter DDL 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 excellent tools for working with regular expressions, so we use

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

The basis 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)); 

ie, breakdown of the SQL file into ranges (Range).

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

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

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

This provides us with convenient and fast 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 (the Range class from the FastColoredTextBox library), but instead of a 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 entries from the second list of ranges to the first. This is done in the method

 private void FindUsage(string regex) 

see class Form1.cs

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

Important note

Because this way 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 limitations 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 the “CREATE TABLE”, we will get the #TMP table together with the CREATE PROC in the results of the match.

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

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

Different and tasty

I would like in the future to add support for MSAGL for visualization of dependencies and greater visibility!

FastColoredTextBox is a hi-end, complete breakthrough! I did not even expect him to have so many interesting features!

→ Sources
→ Build version (archive with exe file)