Use SQL to Find a String in Database Tables, Stored Procedures, and User Defined Functions

Problem

Searching for a string in database tables has been discussed over the years. There are several approaches to the problem (see References at the end of the article). See how to use SQL to find a string in different types of SQL Server objects and data.

Solution

This article shows how to search for a string in tables, stored procedures, and user defined functions. A previous article (Find a String in a Table in SQL Server), only uses native string types like CHAR, NCHAR, VARCHAR, and NVARCHAR. The AdventureWorks database defines several user-defined types that are based on string types. If a column has such a type, it will be included in the search.

From a user perspective, tables and views are similar if we see them as the final result of a SELECT statement. The reason I included views in the search is that a view can contain information from other tables or can have columns, which are composed of several columns from other tables, views, including system tables and views, such that they might contain information which is not in user tables.

This article extends the previous one in several ways:

The search has been extended to select multiple tables in a database where the string will be searched.
The search has been extended, such that the string is searched in tables with user defined-type columns.
The search considers not only tables in the default schema, but also tables in different schemas. A good example of such a database is AdventureWorks.
The search looks at views, stored procedures, and user defined functions, as you can see in the image below:

Prerequisites

SQL Server Enterprise 2019
SQL Server Developer 2022
Visual Studio 2022
Microsoft Office 365
.NET (latest)
Windows 11

Get Information from SQL Server

Since it is much easier to retrieve information from an SQL Server related to different objects, like databases, tables, columns, etc., we will use SQL Server Management Objects (SMO):

using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data;

When we select a database in which we want to search for the string, we get a list of objects in which we want to search. Because a table and a view are similar as the final result of the SELECT statement, we will combine them in one list. Similarly, stored procedures and UDF have a body where we will perform the search, so we will combine them in the same list.

The latest SMO and .NET require that several objects need to be declared as nullable. Declare these objects accordingly:

ServerConnection? _serverConnection;
Server? _server;
Database? _db;
SqlConnection? _conn;

After connecting to SQL Server, select a database from the databaseList list and get a list of user tables, views, stored procedures, and UDFs of that database:

_databaseName = databaseList.SelectedItem.ToString()!;
_db = _server!.Databases[_databaseName];
TableCollection tables = _db.Tables;
StoredProcedureCollection storedProcedures = _db.StoredProcedures;
UserDefinedFunctionCollection userDefinedFunctions = _db.UserDefinedFunctions;
ViewCollection views = _db.Views;
tableList.Items.Clear();
tableList.Items.Add(_allTables);
foreach(Table tbl in tables)
{
    if(!tbl.IsSystemObject)
    {
        tableList.Items.Add("[" + tbl.Schema + "].[" + tbl.Name + "]");
    }
}
if(tableList.Items.Count <= 1)
{
    MessageBox.Show("Database [" + _databaseName + "] has no user tables. Choose another one");
    return;
}
// Views are virtual tables, so add them to the list of tables
if(views.Count > 0)
{
    foreach(Microsoft.SqlServer.Management.Smo.View view in views)
    {
        if(!view.IsSystemObject && !view.IsEncrypted)
        {
            tableList.Items.Add("[" + view.Schema + "].[" + view.Name + "]");
        }
    }
}
labelTableList.Visible = true;
tableList.Visible = true;
storedProceduresList.Items.Clear();
// If we have stored procedures or UDF then enable the selection
if(storedProcedures.Count > 0 || userDefinedFunctions.Count > 0)
{
    // Add stored procedures
    foreach(StoredProcedure sp in storedProcedures)
    {
        if(!sp.IsSystemObject && !sp.IsEncrypted)
        {
            storedProceduresList.Items.Add("[" + sp.Schema + "].[" + sp.Name + "]");
        }
    }
    // Add UDF
    foreach(UserDefinedFunction udf in userDefinedFunctions)
    {
        if(!udf.IsSystemObject && !udf.IsEncrypted)
        {
            storedProceduresList.Items.Add("[" + udf.Schema + "].[" + udf.Name + "]");
        }
    }
    if(storedProceduresList.Items.Count > 1) // we have user SP and/or UDF
    {
        labelStoredProcedures.Visible = true;
        storedProceduresList.Visible = true;
    }
}

As you can see, we do not consider encrypted objects, and also skip system objects.

Compared to the previous article I wrote, I have refactored the search in table code to consider the views, as well as when I report where the string has been found, in a table or a view.

foreach(object tbl in tableList.CheckedItems)
{
    // In this case we need to search for table name and schema name
    string tableName = tbl.ToString()!;
    if(tableName == _allTables) // Skip "all tables", because it is not a table name
        continue;
    int foundschema = tableName.IndexOf("].[") + 1;
    string schema = tableName.Substring(1, foundschema - 2);
    string table = tableName.Remove(tableName.Length - 1)[(foundschema + 2)..];
    // In order to avoid extra code we first search for a table
    // If the table is null, it has to be a view
    // If it is not a view either we have an internal error
    string sqlcmd;
    ColumnCollection columns;
    bool isTable = true;
    if(_db!.Tables[table, schema] != null)
    {
        sqlcmd = "select * from [" + _db!.Tables[table, schema].Schema + "].[" +
                _db!.Tables[table, schema].Name + "]";
        columns = _db!.Tables[table, schema].Columns;
    }
    else
    {
        sqlcmd = "select * from [" + _db!.Views[table, schema].Schema + "].[" +
                _db!.Views[table, schema].Name + "]";
        columns = _db!.Views[table, schema].Columns;
        isTable = false;
    }
    bool found = SearchInSingleTable(sqlcmd, columns, tableName, isTable);
    foundstring |= found;
    if(found)
        _nextrow += 6;
    if(_tooManyRows)
    {
        MessageBox.Show("Number of rows in the database exceed number of rows allowed in Excel",
            "Too many rows in database",
            MessageBoxButtons.OK, MessageBoxIcon.Hand);
         break;
    }
}

In the previous article, I ignored the columns that have a type other than string. After I analyzed the AdventureWorks database, I saw that there are several tables with user defined type, and those types are based on the string type.

foreach(Column col in columns)
{
    /* This is the code in the previous article:
    // Ignore columns which are not string
    if(!col.DataType.IsStringType)
        continue;
    */
    // If a column is not a string, check if it has a user defined type which might be a string
    if(!col.DataType.IsStringType)
    {
        if(col.DataType.SqlDataType == SqlDataType.UserDefinedDataType)
        {
            UserDefinedDataTypeCollection? uddtc = _db.UserDefinedDataTypes;
            if(uddtc!.Contains(col.DataType.Name))
            {
                UserDefinedDataType udtt = uddtc![col.DataType.Name];
                if(!_stringnames.Contains(udtt.SystemType.ToLower()))
                    continue;
            }
        }
        else
            continue;
    }
    // The rest of the code is the same as in the previous article
}

Searching in a stored procedure or UDF looks like the code below. It is easy to combine them because SMO offers the same class member, TextBody, for both stored procedures and UDFs.

foreach(object sp_udf in storedProceduresList.CheckedItems)
{
    // In this case we need to search for table name and schema name
    string spName = sp_udf.ToString()!;
    int foundschema = spName.IndexOf("].[") + 1;
    string schema = spName.Substring(1, foundschema - 2);
    string sp = spName.Remove(spName.Length - 1)[(foundschema + 2)..];
    string body;
    bool isSP;
    // In order to avoid extra code we first search for a SP
    // If the SP is null, it has to be a UDF
    // If it is not a UDF either we have an internal error
    if(_db!.StoredProcedures[sp, schema] != null)
    {
        body = _db!.StoredProcedures[sp, schema].TextBody;
        isSP = true;
    }
    else
    {
        body = _db!.UserDefinedFunctions[sp, schema].TextBody;
        isSP = false;
    }
    bool found = body.Contains(_stringToSearch, caseSensitive.Checked ?
                 StringComparison.InvariantCulture : StringComparison.InvariantCultureIgnoreCase);
    if(found)
    {
        // found the string
        _xlsWorksheet!.Cells[_nextrow, 1] = "Found string in " +
                 (isSP ? "store procedure" : "user defined function");
        _xlsWorksheet!.Cells[_nextrow, 2] = spName;
        _nextrow++;
    }
    foundstring |= found;
    if(found)
        _nextrow += 6;
    if(_tooManyRows)
    {
        MessageBox.Show("Number of rows in the database exceed number of rows allowed in Excel",
                    "Too many rows in database",
                    MessageBoxButtons.OK, MessageBoxIcon.Hand);
        break;
    }
}

C# Project and Sample Excel File

I attached the C# project used and the Excel file generated by searching in the AdventureWorks database on SQL Server 2022. This example searches for string “manager” in all tables and views, and also in several stored procedures and user functions:

I mentioned that the search in all these objects took about 6 seconds on my laptop. To load and display all tables, views, stored procedures and user defined functions took longer, about 50 seconds. When I used SQL Server 2019 for the same database, the search took similar time, but loading and displaying all the objects mentioned above took only around 4 seconds. This shows a huge performance degradation in SQL Server 2022 compared to SQL Server 2019, for exactly the same database with exactly the same data.

Next Steps

Have you ever been curious about what a system stored procedure or system user defined function looks like? It is strange that even system functions are found in SMO as user defined functions.
Have you ever been curious about whether you can manually execute the code of a system stored procedure or system user defined function in SSMS? In several situations, it is possible, but only when you use a special trick.
See if you can take this application and enhance to meet your specific needs.

References

Resources

Community

Subscribe