Get SQL-Server Stored Procedures with C#

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

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; } } 

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(); } 

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; """; } 

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); } } } 

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; } 

 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(); } 

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

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)); } }