Get SQL-Server Stored Procedures with C#

Introduction

GitHub repository

Requires

• Microsoft Visual Studio 2022
• NET 8 Framework
• (SSMS) SQL Server Management Studio

When a developer is the sole developer on a project which interacts with a SQL-Server database stored procedures the developer knows about all the stored procedures but when taking over a project from another developer it can be tedious to check out all the stored procedures in a database or even worst learning about all stored procedures on a server given the developer has proper permission is view the stored procedures.

Rather than having to open (SSMS) SQL Server Management Studio learn how to view stored procedures for an entire server by database with the following Windows Forms utility project along with a console project which shows how to get these stored procedures without a user interface.

For this to be a useful utility project, stored procedure definitions need to be saved off to files with a .sql extension for both syntax coloring when opened by SSMS, VS-Code or Visual Studio, usually the default program is SSMS.

These files are stored under the application folder\Scripts then a folder with today's date.

Usage

Both the console and Windows Forms projects have an appsettings.json file as shown below.

Change Server\Name to the server you want to retrieve stored procedures from.

{
  "ConnectionsConfiguration": {
    "ActiveEnvironment": "Development",
    "Development": "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWind2024;Integrated Security=True;Encrypt=False"
  },
  "Server": {
    "Name": ".\\SQLEXPRESS"
  }
}

Code

Base code resides in two class projects which the console and Windows Forms project share, CommonLibrary and SqlServerLibrary which can be used in a developer’s projects too. Note there some unused code in SqlServerLibrary class project which the reader may find useful for other task.

ConnectionReader class

Provides access to reader the server name from either frontend projects appsettings.json

internal class ConnectionReader
{

    public static string Get(string dbName)
    {
        var _configuration = Configuration.JsonRoot();

        SqlConnectionStringBuilder builder = new()
        {
            DataSource = _configuration.GetValue<string>("Server:Name"),
            InitialCatalog = dbName,
            IntegratedSecurity = true,
            Encrypt = SqlConnectionEncryptOption.Optional
        };

        return builder.ConnectionString;
    }
}

DatabaseService class

Using Dapper to get database names from the SQL-Server instance set in appsettings.json

public class DatabaseService
{
    private readonly IDbConnection _cn = new SqlConnection(ConnectionString());

    public async Task<List<string>> DatabaseNames() 
        => ( await _cn.QueryAsync<string>(SqlStatements.GetDatabaseNames)).AsList();

    /// <summary>
    /// Get names of databases on selected server excluding system databases
    /// </summary>
    /// <returns></returns>
    public async Task<List<string>> DatabaseNamesFiltered() =>
        (await _cn.QueryAsync<string>(
            """
            SELECT name
            FROM sys.databases
            WHERE name NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
            """))
        .AsList();
}

SqlStatements class

Contains SQL statements setup as read-only strings which are used in both frontend projects.

public class SqlStatements
{
    /// <summary>
    /// Provides column names from the description property for each column in a specified table name
    /// </summary>
    public static string DescriptionStatement =>
        """
        SELECT      col.COLUMN_NAME AS ColumnName,
                    col.ORDINAL_POSITION AS Position,
                    ISNULL(prop.value,'(none)') AS [Description]
          FROM      INFORMATION_SCHEMA.TABLES AS tbl
         INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col
            ON col.TABLE_NAME = tbl.TABLE_NAME
         INNER JOIN sys.columns AS sc
            ON sc.object_id   = OBJECT_ID(tbl.TABLE_SCHEMA + '.' + tbl.TABLE_NAME)
           AND sc.name        = col.COLUMN_NAME
          LEFT JOIN sys.extended_properties prop
            ON prop.major_id  = sc.object_id
           AND prop.minor_id  = sc.column_id
           AND prop.name      = 'MS_Description'
         WHERE      tbl.TABLE_NAME = @TableName
         ORDER BY col.ORDINAL_POSITION;
        """;

    /// <summary>
    /// Get default values for each column for tables using a connection object
    /// </summary>
    public static string GetDefaultValuesInDatabase =>
        """
        SELECT SO.[name] AS "TableName",
               SC.[name] AS "ColumnName",
               SM.[text] AS "DefaultValue"
        FROM dbo.sysobjects SO
            INNER JOIN dbo.syscolumns SC
                ON SO.id = SC.id
            LEFT JOIN dbo.syscomments SM
                ON SC.cdefault = SM.id
        WHERE SO.xtype = 'U'
              AND SO.[name] <> 'sysdiagrams'
              AND SM.[text] IS NOT NULL
        ORDER BY SO.[name],
                 SC.colid;
        """;

    /// <summary>
    /// Get all database names from master
    /// </summary>
    public static string GetDatabaseNames =>
        """
        SELECT TableName = DB_NAME(s_mf.database_id)
        FROM sys.master_files s_mf
        WHERE s_mf.state = 0 -- ONLINE
              AND HAS_DBACCESS(DB_NAME(s_mf.database_id)) = 1
              AND DB_NAME(s_mf.database_id) NOT IN ( 'master', 'tempdb', 'model', 'msdb' )
              AND DB_NAME(s_mf.database_id)NOT LIKE 'ReportServer%'
        GROUP BY s_mf.database_id
        ORDER BY 1;
        """;


    /// <summary>
    /// Get details for a table which requires a table name in the calling code
    /// </summary>
    public static string TableDetails =>
        """
        SELECT c.[name] 'ColumnName',
               t.[name] 'DataType',
               c.[max_length] 'MaxLength',
               c.[precision] 'Precision',
               c.scale 'Scale',
               c.is_nullable 'IsNullable',
               ISNULL(i.is_primary_key, 0) 'PrimaryKey'
        FROM sys.columns c
            INNER JOIN sys.types t
                ON c.user_type_id = t.user_type_id
            LEFT OUTER JOIN sys.index_columns ic
                ON ic.object_id = c.object_id
                   AND ic.column_id = c.column_id
            LEFT OUTER JOIN sys.indexes i
                ON ic.object_id = i.object_id
                   AND ic.index_id = i.index_id
        WHERE c.object_id = OBJECT_ID(@TableName);
        """;

    /// <summary>
    /// Get all computed columns in a database using a valid connection object
    /// </summary>
    public static string ComputedColumnDefinitions =>
        """
        SELECT SCHEMA_NAME(o.schema_id) 'SchemaName',
               c.name AS 'ColumnName',
               OBJECT_NAME(c.object_id) AS 'TableName',
               TYPE_NAME(c.user_type_id) AS 'DataType',
               c.definition 'Definition'
        FROM sys.computed_columns c
            JOIN sys.objects o
                ON o.object_id = c.object_id
        ORDER BY SchemaName,
                 TableName,
                 c.column_id;
        """;

    /// <summary>
    /// Determine if table exists in a data using a table name as a parameter
    /// </summary>
    public static string TableExists =>
        """
        SELECT CASE
            WHEN EXISTS
                 (
                     (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName)
                 ) THEN
                1
            ELSE
                0
        END;
        """;

    /// <summary>
    /// Get names of user stored procedures that start with usp or usp_
    /// </summary>
    public static string GetUserStoredProcedureNames =>
        """
        SELECT [name]
        FROM sys.procedures
        WHERE [name] LIKE 'usp%' OR [name] LIKE 'usp_%';
        """;

    public static string WhereInCustomers =>
        """
        SELECT C.CustomerIdentifier,
               C.CompanyName,
               C.Street,
               C.City,
               C.CountryIdentifier,
               CO.Name AS CountryName,
               C.Phone,
               C.ContactId,
               CT.FirstName,
               CT.LastName
        FROM dbo.Customers AS C
            INNER JOIN dbo.Contacts AS CT
                ON C.ContactId = CT.ContactId
            INNER JOIN dbo.Countries AS CO
                ON C.CountryIdentifier = CO.CountryIdentifier
        WHERE C.CustomerIdentifier IN ({0})
        ORDER BY C.CompanyName
        """;

    /// <summary>
    /// Get all date time columns for tables in database
    /// </summary>
    public static string GetAllDateTimeColumnsInDatabase =>
        """
        SELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS [TableName],
               c.column_id "ColumnId",
               c.name AS "ColumnName",
               TYPE_NAME(c.user_type_id) AS DataType,
               c.scale AS "Scale"
        FROM sys.columns c
            JOIN sys.tables t
                ON t.object_id = c.object_id
        WHERE TYPE_NAME(c.user_type_id) IN ( 'date', 'datetimeoffset', 'datetime2', 'smalldatetime', 'datetime', 'time' )
        ORDER BY [TableName],
                 c.column_id;
        """;

    /// <summary>
    /// Get details for database tables
    /// Table name, constraint name, primary key column name, foreign table, foreign key column update rule delete rule 
    /// </summary>
    public static string TableConstraintsForDatabase =>
        """
        SELECT
            PrimaryKeyTable = QUOTENAME(PK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(PK.TABLE_NAME),
            ConstraintName = C.CONSTRAINT_NAME,
            PrimaryKeyColumn = CCU.COLUMN_NAME,
            ForeignKeyTable = QUOTENAME(FK.CONSTRAINT_SCHEMA) + '.' + QUOTENAME(FK.TABLE_NAME),
            ForeignKeyColumn = CU.COLUMN_NAME,
            UpdateRule = C.UPDATE_RULE,
            DeleteRule = C.DELETE_RULE
        FROM
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
                C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND
                C.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND
                C.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
                C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND
                C.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND
                C.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON
                C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME AND
                C.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG AND
                C.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
            INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
                PK.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME AND
                PK.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG AND
                PK.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
        WHERE
            FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
        ORDER BY
            PK.TABLE_NAME,
            FK.TABLE_NAME
        """;

    /// <summary>
    /// Select for Customers which requires parameters for CustomerIdentifier, PhoneType and ContactType
    /// </summary>
    public static string GetCustomers =>
        """
        SELECT Cust.CustomerIdentifier,
               Cust.CompanyName,
               Cust.City,
               Cust.PostalCode,
               C.ContactId,
               CO.CountryIdentifier,
               CO.Name AS Country,
               Cust.Phone,
               Devices.PhoneTypeIdentifier,
               Devices.PhoneNumber,
               Cust.ContactTypeIdentifier,
               C.FirstName,
               C.LastName,
               CT.ContactTitle
        FROM dbo.Customers AS Cust
            INNER JOIN dbo.ContactType AS CT
                ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
            INNER JOIN dbo.Countries AS CO
                ON Cust.CountryIdentifier = CO.CountryIdentifier
            INNER JOIN dbo.Contacts AS C
                ON Cust.ContactId = C.ContactId
            INNER JOIN dbo.ContactDevices AS Devices
                ON C.ContactId = Devices.ContactId
        WHERE (
                  Cust.CustomerIdentifier = @CustomerIdentifier
                  AND Devices.PhoneTypeIdentifier = @PhoneType
                  AND Cust.ContactTypeIdentifier = @ContactType
              );
        """;

    public static string TableNamesForDatabase(string databaseName) => 
        $"""
         SELECT TABLE_NAME AS TableName
          FROM [{databaseName}].INFORMATION_SCHEMA.TABLES
         WHERE TABLE_TYPE = 'BASE TABLE'
           AND TABLE_NAME <> 'sysdiagrams'
         ORDER BY TABLE_NAME;
         """;

    public static string DatabaseTablesRowCount => 
        """
        SELECT     QUOTENAME(SCHEMA_NAME(item.schema_id)) + '.' + QUOTENAME(item.name) AS [Name],
                   SUM(parts.[rows]) AS [RowCount]
         FROM      sys.objects AS item
        INNER JOIN sys.partitions AS parts
           ON item.object_id = parts.object_id
        WHERE      item.[type]     = 'U'
          AND      item.is_ms_shipped = 0x0
          AND      parts.index_id      < 2 -- 0:Heap, 1:Clustered
          AND  item.[name] <> 'sysdiagrams'
        GROUP BY item.schema_id,
                 item.[name]
        ORDER BY [Name];
        """;

    public static string DatabaseTablesRowCount1 =>
        """
        SELECT     TableSchema = s.name,
                   Name = t.name,
                   [RowCount] = p.rows
         FROM      sys.tables t
        INNER JOIN sys.schemas s
           ON t.schema_id = s.schema_id
        INNER JOIN sys.indexes i
           ON t.object_id = i.object_id
        INNER JOIN sys.partitions p
           ON i.object_id = p.object_id
          AND i.index_id  = p.index_id
        WHERE      t.is_ms_shipped = 0
        GROUP BY t.name,
                 s.name,
                 p.rows
        ORDER BY s.name,
                 t.name;
        """;
}

ListDictionary class

This class is used to store database names, table names and user stored procedures. Note the .NET Framework also has a ListDictionary class so be careful when using in other projects to reference this one, not the .NET Framework version.

public class ListDictionary
{
    private Dictionary<string, List<string>> _internalDictionary = new();
    public Dictionary<string, List<string>> Dictionary 
        => _internalDictionary;

    public bool HasItems => _internalDictionary.Count > 0;

    public void Add(string key, string value)
    {
        if (_internalDictionary.TryGetValue(key, out var item))
        {
            if (item.Contains(value) == false)
            {
                item.Add(value);
            }
        }
        else
        {
            List<string> list = [value];
            _internalDictionary.Add(key, list);
        }
    }
}

Core models

These models are used to stored information for each database user stored procedures

public class DatabaseContainer
{
    public string Database { get; set; }
    public List<ProcedureContainer> List { get; set; } = new();
    public override string ToString() => Database;

}


public class ProcedureContainer
{
    public string Procedure { get; set; }
    public string Definition { get; set; }
    public override string ToString() => Procedure;
}

Code cycle through a database

The following code uses the models shown above to stored stored procedures. This code lays the foundation for use in other projects.

public static async Task<List<DatabaseContainer>> GetStoredProcedureDetails()
{
    ListDictionary listDictionary = new();
    StoredProcedureHelpers helpers = new();

    List<DatabaseContainer> databaseContainers = new();

    var service = new DatabaseService();
    List<string> dbNames = await service.DatabaseNamesFiltered();

    dbNames = dbNames.OrderBy(x => x).ToList();
    foreach (var dbName in dbNames)
    {

        var (hasStoredProcedures, list) = await helpers
            .GetStoredProcedureNameSafe(dbName, "'xp_', 'ms_'");

        if (hasStoredProcedures)
        {

            var root = databaseContainers.FirstOrDefault(x 
                => x.Database == dbName);

            DatabaseContainer container = new DatabaseContainer { Database = dbName };
            if (root is null)
            {
                container = new DatabaseContainer { Database = dbName };
            }


            foreach (var item in list)
            {
                var definition = await helpers.GetStoredProcedureDefinitionAsync(dbName, item);
                if (definition is not null && !item.Contains("diagram"))
                {
                    listDictionary.Add(dbName, item);
                    container.List.Add(new ProcedureContainer
                    {
                        Procedure = item, 
                        Definition = definition
                    });
                }

            }

            databaseContainers.Add(container);
        }
    }

    return databaseContainers.Where(x => x.List.Count > 0).ToList();
}

Windows Form project

There really is nothing to change here other than how user stored procedures are saved if so desire.

In the project file the following creates a Scripts folder beneath the application folder after a build is performed to stored user stored procedures.

<Target Name="MakeScriptsDir" AfterTargets="Build">
   <MakeDir Directories="$(OutDir)Scripts\$([System.DateTime]::Now.ToString(yyyy-MM-dd))" />
</Target>

Note
Changing the above requires change the code in the Form SaveButton which expects the folder above to be present.

Special notes

• Anytime a developer writes code in a form avoid best as possible to reference controls. In the code provided a BindingList is used to limit touching controls. One exception which is a no-no is where the code passes the RichTextBox to a class, in this case it was done to limit code in the form which my rule is no more than 120 lines of code.
• Code reuse which is done for events in the form. Always think how can I not duplicate code in a form or class.

Saving stored procedures

Once a database has been selected, click the save button.

1. current variable contain everything needed accept the stored procedure definitions. The List property has the stored procedure names.
2. Next two variable are used to get the SQL-Server instance nam
3. folder variable points to the folder defined in the project file described above.
4. The foreach iterates stored procedure names are reads back each stored procedure definition and write each one to a file.

private void SaveButton_Click(object sender, EventArgs e)
{
    var current = _bindingList[DatabaseComboBox.SelectedIndex];
    var _configuration = Configuration.JsonRoot();
    var serverName = _configuration.GetValue<string>("Server:Name").CleanFileName();

    var folder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Scripts",
        $"{Now.Year}-{Now.Month:d2}-{Now.Day:d2}" );

    foreach (var item in current.List)
    {
        File.WriteAllText(Path.Combine(folder, $"{serverName}_{current.Name}_{item}.sql"), 
            _helpers.GetStoredProcedureDefinition(DatabaseComboBox.Text, item));
    }
}

Summary

What has been presented provides an easy way to discover and save off user stored procedures for a SQL-Server instance. Besides being a useful tool just above any level developer can learn new techniques and SQL usage.

GitHub repository

Why use stored procedures

Stored procedures can be beneficial in C# because they can improve application performance, make code easier to maintain, and encapsulate complex logic in the database. They can also be used to perform a variety of tasks, including retrieving, updating, and deleting data.

Stored procedures are not vulnerable to SQL Injection attacks and are more secure than dynamic SQL when multiple applications access the database.

Should I use stored procedures?

Do the research and decide for yourself.

Support for stored procedures

• All data providers
• EF Core
• Dapper