Searching for text in sql server stored procedures

Question

Hi, I have created a program that allows me to search for keywords inside the code of stored procedures, functions, tables etc. in SQL Server generated text files. I am looking for a way to find out where a search term (i.e "ADMIN.TABLE) is being referenced in all the code files. I use LINQ to find references. For example when I search for ADMIN.TABLE, I will list the name of the text files that contains a referenceto the search term "ADMIN.TABLE" somewhere in the text. However, the search is not absolute in that unless you include different variations of the ADMIN.TABLE you will not find all references. So to find all references I need to do something like this: 1) Search for "ADMIN.TABLE" 2) Search for "[ADMIN].TABLE" 3)Search for "ADMIN.[TABLE]" 4) Search for "[ADMIN].[TABLE]"

I am thinking of using a regular expression pattern, but I am not sure how to construct one that will cover the different combinations listed above. Any help would be appreciated.

Regards,

Tor

Answer 1

Eh... ? means match previous group 0 or 1 times. Both [ and ] have special meaning, so you have to escape them with . So:

new Regex(@"\[?ADMIN\]?.\[?TABLE\]?")

Would do was you ask.

Answer 2

I think you may be reinventing the wheel a bit. Have you looked at sp_depends? I will list all of the objects which depend on a specified table.

I realize that you're working with text files, but if you could generate the sp_depends results along with the scripts, that might solve your problem.

Answer 3

Try this, it will loop through all the files in a directory and scan them using a case-insensitive regular expression (you could change the Directory.GetFiles call to only search the directory without checking sub directories by changing the search option):

        string pathWithSqlFiles = @"c:\sqlfiles\";
        string[] files = System.IO.Directory.GetFiles(pathWithSqlFiles, "*.sql", System.IO.SearchOption.AllDirectories);
        string regexToSearch = @"\[?admin\]?.\[?table\]?";
        foreach (string file in files)
        {
            string fileText = System.IO.File.ReadAllText(file);
            System.Text.RegularExpressions.Match match = System.Text.RegularExpressions.Regex.Match(fileText, regexToSearch, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            if (match.Success)
            {
                // do logic to handle the matched text
            }
        }

Answer 4

If you are not set on how to go about it, you may want to parse the sql scripts and then use the object model to specifically find schema=admin && table=table.

Option 1: Generate the estimated query plan and parse the table references out of it. If you're on sql 2005+ you can get it as xml and query like:

;WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select
    t.n.value('./@Database', 'sysname') as DatabaseName,
    t.n.value('./@Schema', 'sysname') as SchemaName,
    t.n.value('./@Table', 'sysname') as TableName,
    t.n.value('./@Column', 'sysname') as ColName
from @x.nodes('//ColumnReference') as t(n)
go

Option 2:

If you want to do this work on the client side and have sql2008, add a reference to C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.SqlParser.dll and do like

    SqlScript script = Parser.Parse(@"create proc sp1 as select 'abc' as abc1");

Then you can process script.Xml and look for a node like I'm sure you can find it strongly typed in the object model too since I see SqlTableRefExpression in the assembly, but I'm not positive where to drill down to.