# Directory Structure
```
├── .github
│ └── copilot-instructions.md
├── .gitignore
├── appsettings.json
├── Helpers
│ └── SchemaHelper.cs
├── LICENSE
├── MsSqlMCP.csproj
├── MsSqlMCP.sln
├── Program.cs
├── README.md
└── SchemaTool.cs
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | ## Ignore Visual Studio temporary files, build results, and
2 | ## files generated by popular Visual Studio add-ons.
3 | ##
4 | ## Get latest from https://github.com/github/gitignore/blob/main/VisualStudio.gitignore
5 |
6 | # User-specific files
7 | *.rsuser
8 | *.suo
9 | *.user
10 | *.userosscache
11 | *.sln.docstates
12 |
13 | # User-specific files (MonoDevelop/Xamarin Studio)
14 | *.userprefs
15 |
16 | # Mono auto generated files
17 | mono_crash.*
18 |
19 | # Build results
20 | [Dd]ebug/
21 | [Dd]ebugPublic/
22 | [Rr]elease/
23 | [Rr]eleases/
24 | x64/
25 | x86/
26 | [Ww][Ii][Nn]32/
27 | [Aa][Rr][Mm]/
28 | [Aa][Rr][Mm]64/
29 | bld/
30 | [Bb]in/
31 | [Oo]bj/
32 | [Ll]og/
33 | [Ll]ogs/
34 |
35 | # Visual Studio 2015/2017 cache/options directory
36 | .vs/
37 | # Uncomment if you have tasks that create the project's static files in wwwroot
38 | #wwwroot/
39 |
40 | # Visual Studio 2017 auto generated files
41 | Generated\ Files/
42 |
43 | # MSTest test Results
44 | [Tt]est[Rr]esult*/
45 | [Bb]uild[Ll]og.*
46 |
47 | # NUnit
48 | *.VisualState.xml
49 | TestResult.xml
50 | nunit-*.xml
51 |
52 | # Build Results of an ATL Project
53 | [Dd]ebugPS/
54 | [Rr]eleasePS/
55 | dlldata.c
56 |
57 | # Benchmark Results
58 | BenchmarkDotNet.Artifacts/
59 |
60 | # .NET Core
61 | project.lock.json
62 | project.fragment.lock.json
63 | artifacts/
64 |
65 | # ASP.NET Scaffolding
66 | ScaffoldingReadMe.txt
67 |
68 | # StyleCop
69 | StyleCopReport.xml
70 |
71 | # Files built by Visual Studio
72 | *_i.c
73 | *_p.c
74 | *_h.h
75 | *.ilk
76 | *.meta
77 | *.obj
78 | *.iobj
79 | *.pch
80 | *.pdb
81 | *.ipdb
82 | *.pgc
83 | *.pgd
84 | *.rsp
85 | # but not Directory.Build.rsp, as it configures directory-level build defaults
86 | !Directory.Build.rsp
87 | *.sbr
88 | *.tlb
89 | *.tli
90 | *.tlh
91 | *.tmp
92 | *.tmp_proj
93 | *_wpftmp.csproj
94 | *.log
95 | *.tlog
96 | *.vspscc
97 | *.vssscc
98 | .builds
99 | *.pidb
100 | *.svclog
101 | *.scc
102 |
103 | # Chutzpah Test files
104 | _Chutzpah*
105 |
106 | # Visual C++ cache files
107 | ipch/
108 | *.aps
109 | *.ncb
110 | *.opendb
111 | *.opensdf
112 | *.sdf
113 | *.cachefile
114 | *.VC.db
115 | *.VC.VC.opendb
116 |
117 | # Visual Studio profiler
118 | *.psess
119 | *.vsp
120 | *.vspx
121 | *.sap
122 |
123 | # Visual Studio Trace Files
124 | *.e2e
125 |
126 | # TFS 2012 Local Workspace
127 | $tf/
128 |
129 | # Guidance Automation Toolkit
130 | *.gpState
131 |
132 | # ReSharper is a .NET coding add-in
133 | _ReSharper*/
134 | *.[Rr]e[Ss]harper
135 | *.DotSettings.user
136 |
137 | # TeamCity is a build add-in
138 | _TeamCity*
139 |
140 | # DotCover is a Code Coverage Tool
141 | *.dotCover
142 |
143 | # AxoCover is a Code Coverage Tool
144 | .axoCover/*
145 | !.axoCover/settings.json
146 |
147 | # Coverlet is a free, cross platform Code Coverage Tool
148 | coverage*.json
149 | coverage*.xml
150 | coverage*.info
151 |
152 | # Visual Studio code coverage results
153 | *.coverage
154 | *.coveragexml
155 |
156 | # NCrunch
157 | _NCrunch_*
158 | .*crunch*.local.xml
159 | nCrunchTemp_*
160 |
161 | # MightyMoose
162 | *.mm.*
163 | AutoTest.Net/
164 |
165 | # Web workbench (sass)
166 | .sass-cache/
167 |
168 | # Installshield output folder
169 | [Ee]xpress/
170 |
171 | # DocProject is a documentation generator add-in
172 | DocProject/buildhelp/
173 | DocProject/Help/*.HxT
174 | DocProject/Help/*.HxC
175 | DocProject/Help/*.hhc
176 | DocProject/Help/*.hhk
177 | DocProject/Help/*.hhp
178 | DocProject/Help/Html2
179 | DocProject/Help/html
180 |
181 | # Click-Once directory
182 | publish/
183 |
184 | # Publish Web Output
185 | *.[Pp]ublish.xml
186 | *.azurePubxml
187 | # Note: Comment the next line if you want to checkin your web deploy settings,
188 | # but database connection strings (with potential passwords) will be unencrypted
189 | *.pubxml
190 | *.publishproj
191 |
192 | # Microsoft Azure Web App publish settings. Comment the next line if you want to
193 | # checkin your Azure Web App publish settings, but sensitive information contained
194 | # in these scripts will be unencrypted
195 | PublishScripts/
196 |
197 | # NuGet Packages
198 | *.nupkg
199 | # NuGet Symbol Packages
200 | *.snupkg
201 | # The packages folder can be ignored because of Package Restore
202 | **/[Pp]ackages/*
203 | # except build/, which is used as an MSBuild target.
204 | !**/[Pp]ackages/build/
205 | # Uncomment if necessary however generally it will be regenerated when needed
206 | #!**/[Pp]ackages/repositories.config
207 | # NuGet v3's project.json files produces more ignorable files
208 | *.nuget.props
209 | *.nuget.targets
210 |
211 | # Microsoft Azure Build Output
212 | csx/
213 | *.build.csdef
214 |
215 | # Microsoft Azure Emulator
216 | ecf/
217 | rcf/
218 |
219 | # Windows Store app package directories and files
220 | AppPackages/
221 | BundleArtifacts/
222 | Package.StoreAssociation.xml
223 | _pkginfo.txt
224 | *.appx
225 | *.appxbundle
226 | *.appxupload
227 |
228 | # Visual Studio cache files
229 | # files ending in .cache can be ignored
230 | *.[Cc]ache
231 | # but keep track of directories ending in .cache
232 | !?*.[Cc]ache/
233 |
234 | # Others
235 | ClientBin/
236 | ~$*
237 | *~
238 | *.dbmdl
239 | *.dbproj.schemaview
240 | *.jfm
241 | *.pfx
242 | *.publishsettings
243 | orleans.codegen.cs
244 |
245 | # Including strong name files can present a security risk
246 | # (https://github.com/github/gitignore/pull/2483#issue-259490424)
247 | #*.snk
248 |
249 | # Since there are multiple workflows, uncomment next line to ignore bower_components
250 | # (https://github.com/github/gitignore/pull/1529#issuecomment-104372622)
251 | #bower_components/
252 |
253 | # RIA/Silverlight projects
254 | Generated_Code/
255 |
256 | # Backup & report files from converting an old project file
257 | # to a newer Visual Studio version. Backup files are not needed,
258 | # because we have git ;-)
259 | _UpgradeReport_Files/
260 | Backup*/
261 | UpgradeLog*.XML
262 | UpgradeLog*.htm
263 | ServiceFabricBackup/
264 | *.rptproj.bak
265 |
266 | # SQL Server files
267 | *.mdf
268 | *.ldf
269 | *.ndf
270 |
271 | # Business Intelligence projects
272 | *.rdl.data
273 | *.bim.layout
274 | *.bim_*.settings
275 | *.rptproj.rsuser
276 | *- [Bb]ackup.rdl
277 | *- [Bb]ackup ([0-9]).rdl
278 | *- [Bb]ackup ([0-9][0-9]).rdl
279 |
280 | # Microsoft Fakes
281 | FakesAssemblies/
282 |
283 | # GhostDoc plugin setting file
284 | *.GhostDoc.xml
285 |
286 | # Node.js Tools for Visual Studio
287 | .ntvs_analysis.dat
288 | node_modules/
289 |
290 | # Visual Studio 6 build log
291 | *.plg
292 |
293 | # Visual Studio 6 workspace options file
294 | *.opt
295 |
296 | # Visual Studio 6 auto-generated workspace file (contains which files were open etc.)
297 | *.vbw
298 |
299 | # Visual Studio 6 auto-generated project file (contains which files were open etc.)
300 | *.vbp
301 |
302 | # Visual Studio 6 workspace and project file (working project files containing files to include in project)
303 | *.dsw
304 | *.dsp
305 |
306 | # Visual Studio 6 technical files
307 | *.ncb
308 | *.aps
309 |
310 | # Visual Studio LightSwitch build output
311 | **/*.HTMLClient/GeneratedArtifacts
312 | **/*.DesktopClient/GeneratedArtifacts
313 | **/*.DesktopClient/ModelManifest.xml
314 | **/*.Server/GeneratedArtifacts
315 | **/*.Server/ModelManifest.xml
316 | _Pvt_Extensions
317 |
318 | # Paket dependency manager
319 | .paket/paket.exe
320 | paket-files/
321 |
322 | # FAKE - F# Make
323 | .fake/
324 |
325 | # CodeRush personal settings
326 | .cr/personal
327 |
328 | # Python Tools for Visual Studio (PTVS)
329 | __pycache__/
330 | *.pyc
331 |
332 | # Cake - Uncomment if you are using it
333 | # tools/**
334 | # !tools/packages.config
335 |
336 | # Tabs Studio
337 | *.tss
338 |
339 | # Telerik's JustMock configuration file
340 | *.jmconfig
341 |
342 | # BizTalk build output
343 | *.btp.cs
344 | *.btm.cs
345 | *.odx.cs
346 | *.xsd.cs
347 |
348 | # OpenCover UI analysis results
349 | OpenCover/
350 |
351 | # Azure Stream Analytics local run output
352 | ASALocalRun/
353 |
354 | # MSBuild Binary and Structured Log
355 | *.binlog
356 |
357 | # NVidia Nsight GPU debugger configuration file
358 | *.nvuser
359 |
360 | # MFractors (Xamarin productivity tool) working folder
361 | .mfractor/
362 |
363 | # Local History for Visual Studio
364 | .localhistory/
365 |
366 | # Visual Studio History (VSHistory) files
367 | .vshistory/
368 |
369 | # BeatPulse healthcheck temp database
370 | healthchecksdb
371 |
372 | # Backup folder for Package Reference Convert tool in Visual Studio 2017
373 | MigrationBackup/
374 |
375 | # Ionide (cross platform F# VS Code tools) working folder
376 | .ionide/
377 |
378 | # Fody - auto-generated XML schema
379 | FodyWeavers.xsd
380 |
381 | # VS Code files for those working on multiple tools
382 | .vscode/*
383 | !.vscode/settings.json
384 | !.vscode/tasks.json
385 | !.vscode/launch.json
386 | !.vscode/extensions.json
387 | *.code-workspace
388 |
389 | # Local History for Visual Studio Code
390 | .history/
391 |
392 | # Windows Installer files from build outputs
393 | *.cab
394 | *.msi
395 | *.msix
396 | *.msm
397 | *.msp
398 |
399 | # JetBrains Rider
400 | *.sln.iml
401 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # MsSqlMCP
2 | MCP to query SQL Server database schema, such as tables, columns, and relationships
3 |
4 | ## Prerequisites:
5 | - .NET 9
6 | - SQL
7 |
8 | ## Configuration
9 | To configure Copilot in Visual Studio Code, add the MCP server configuration to the `settings.json` file:
10 |
11 | ```json
12 | "mcp": {
13 | "inputs": [],
14 | "servers": {
15 | "MsSqlMCP": {
16 | "type": "stdio",
17 | "command": "dotnet",
18 | "args": [
19 | "run",
20 | "--project",
21 | "c:\\{path of repository}\\MsSqlMCP\\MsSqlMCP.csproj"
22 | ]
23 | }
24 | }
25 | }
26 |
```
--------------------------------------------------------------------------------
/appsettings.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "ConnectionStrings": {
3 | "DefaultConnection": "Data Source=your_database_server;Initial Catalog=your_database;Encrypt=False;Trusted_Connection=True;MultipleActiveResultSets=true"
4 | }
5 |
6 | }
7 |
```
--------------------------------------------------------------------------------
/Program.cs:
--------------------------------------------------------------------------------
```csharp
1 | namespace MsSqlMCP
2 | {
3 | using Microsoft.Extensions.Configuration;
4 | using Microsoft.Extensions.DependencyInjection;
5 | using Microsoft.Extensions.Hosting;
6 | using Microsoft.Extensions.Logging;
7 |
8 | public static class Program
9 | {
10 | public static (string connectionString, bool warning) GetConnectionString()
11 | {
12 |
13 | IConfigurationRoot configuration = new ConfigurationBuilder()
14 | .SetBasePath(AppContext.BaseDirectory)
15 | .AddJsonFile("appsettings.json", optional: false)
16 | .AddEnvironmentVariables()
17 | .Build();
18 |
19 | string? connectionString = configuration.GetConnectionString("DefaultConnection");
20 | if (string.IsNullOrEmpty(connectionString))
21 | {
22 | throw new InvalidOperationException("Connection string 'DefaultConnection' not found in appsettings.json");
23 | }
24 |
25 | return (connectionString, false);
26 | }
27 | public static async Task Main(string[] args)
28 | {
29 | var builder = Host.CreateApplicationBuilder(args);
30 | builder.Logging.AddConsole(consoleLogOptions =>
31 | {
32 | // Configure all logs to go to stderr
33 | consoleLogOptions.LogToStandardErrorThreshold = LogLevel.Trace;
34 | });
35 |
36 | // Get connection string from appsettings.json to anticipate error
37 | GetConnectionString();
38 |
39 | builder.Services
40 | .AddMcpServer()
41 | .WithStdioServerTransport()
42 | .WithToolsFromAssembly();
43 |
44 |
45 | var app = builder.Build();
46 |
47 | await app.RunAsync();
48 | }
49 |
50 | }
51 | }
52 |
```
--------------------------------------------------------------------------------
/.github/copilot-instructions.md:
--------------------------------------------------------------------------------
```markdown
1 | # MsSqlMCP - AI Coding Agent Instructions
2 |
3 | ## Project Overview
4 | MsSqlMCP is a Model Context Protocol (MCP) server that provides SQL Server database schema exploration tools for AI agents. It exposes database metadata through MCP tools that can be called by AI assistants.
5 |
6 | ## Architecture & Key Components
7 |
8 | ### Core Pattern: MCP Tool Registration
9 | - Tools are defined as static methods in `SchemaTool.cs` decorated with `[McpServerTool]`
10 | - Each tool method is automatically registered via `WithToolsFromAssembly()` in `Program.cs`
11 | - Tools follow async pattern: `public async static Task<string> ToolName(params)`
12 |
13 | ### Database Connection Strategy
14 | - Connection string loaded from `appsettings.json` via `Program.GetConnectionString()`
15 | - Always use `using var connection = new SqlConnection(connectionString)` pattern
16 | - Database switching handled via `USE {SanitizeDatabaseName(databaseName)}` commands
17 | - **Critical**: All database names must be sanitized using `SanitizeDatabaseName()` to prevent SQL injection
18 |
19 | ### Helper Layer Pattern
20 | - `SchemaHelper.cs` contains all SQL queries and data access logic
21 | - Tools in `SchemaTool.cs` handle MCP concerns (connection, database switching, formatting)
22 | - Separation: SchemaTool = MCP interface, SchemaHelper = data access
23 |
24 | ## Development Workflows
25 |
26 | ### Adding New MCP Tools
27 | 1. Add method to `SchemaTool.cs` with `[McpServerTool, Description("...")]`
28 | 2. Implement data access method in `SchemaHelper.cs`
29 | 3. Follow connection pattern: open connection, switch database if needed, call helper
30 | 4. Return formatted string (not JSON) - MCP handles serialization
31 |
32 | ### Running & Testing
33 | ```bash
34 | dotnet run --project MsSqlMCP.csproj
35 | ```
36 | - Configure in VS Code `settings.json` under `"mcp"` section
37 | - Test via Copilot chat which calls the MCP tools
38 |
39 | ### Configuration
40 | - Database connection in `appsettings.json` under `ConnectionStrings.DefaultConnection`
41 | - Supports Windows Authentication (`Trusted_Connection=True`) and SQL auth
42 | - `appsettings.json` copied to output directory via `.csproj` configuration
43 |
44 | ## Code Conventions
45 |
46 | ### SQL Injection Prevention
47 | - **Always** use `SanitizeDatabaseName()` for dynamic database names
48 | - Use parameterized queries (`@parameter`) for user input in SchemaHelper
49 | - Example: `command.Parameters.AddWithValue("@tableName", tableName)`
50 |
51 | ### SQL Query Restrictions
52 | - `ExecuteSql` only allows read-only operations (SELECT, SHOW, DESCRIBE, etc.)
53 | - Blocked statements: DROP, INSERT, UPDATE, DELETE, TRUNCATE, ALTER, CREATE, EXEC, EXECUTE, MERGE, BULK, BACKUP, RESTORE, DBCC, GRANT, DENY, REVOKE, USE, SHUTDOWN, KILL, RECONFIGURE
54 | - Multi-statement queries are validated to prevent restriction bypass
55 | - This ensures the tool remains a safe schema exploration utility
56 |
57 | ### Error Handling Pattern
58 | ```csharp
59 | try {
60 | // SQL operations
61 | } catch (SqlException ex) {
62 | return $"SQL Error: {ex.Message}";
63 | } catch (Exception ex) {
64 | return $"Error: {ex.Message}";
65 | }
66 | ```
67 |
68 | ### Query Result Formatting
69 | - Tables: `schema.tablename` format
70 | - Columns: `name | datatype(length) | NULL/NOT NULL IDENTITY PRIMARY KEY`
71 | - Relationships: `table.column -> referenced_table.referenced_column (FK: name)`
72 |
73 | ## Key Dependencies
74 | - `ModelContextProtocol` (v0.1.0-preview.9): Core MCP framework
75 | - `Microsoft.Data.SqlClient`: SQL Server connectivity
76 | - `Microsoft.Extensions.Hosting`: Dependency injection and configuration
77 |
78 | ## Schema Query Patterns
79 | - Use `INFORMATION_SCHEMA` views for portable metadata queries
80 | - Use `sys.*` views for SQL Server-specific features (foreign keys, procedures)
81 | - Always include schema prefix in table references
82 | - Order results consistently for predictable output
83 |
84 | ## Security Considerations
85 | - All data-modifying statements explicitly blocked in `ExecuteSql`: DROP, INSERT, UPDATE, DELETE, TRUNCATE, ALTER, CREATE, EXEC/EXECUTE, MERGE, BULK, BACKUP, RESTORE, DBCC, GRANT, DENY, REVOKE, USE, SHUTDOWN, KILL, RECONFIGURE
86 | - Multi-statement queries are validated to prevent bypassing restrictions
87 | - Database name sanitization prevents injection
88 | - Connection string supports both Windows and SQL authentication
89 | - Only read-only operations permitted - this is a schema exploration tool only
90 | - No direct file system access - database operations only
91 |
```
--------------------------------------------------------------------------------
/Helpers/SchemaHelper.cs:
--------------------------------------------------------------------------------
```csharp
1 | using Microsoft.Data.SqlClient;
2 |
3 | namespace MsSqlMCP.Helpers
4 | {
5 | public class SchemaHelper
6 | {
7 |
8 |
9 | public async Task<List<string>> GetTablesAsync(SqlConnection connection)
10 | {
11 | var tables = new List<string>();
12 |
13 | string sql = @"
14 | SELECT TABLE_SCHEMA, TABLE_NAME
15 | FROM INFORMATION_SCHEMA.TABLES
16 | WHERE TABLE_TYPE = 'BASE TABLE'
17 | ORDER BY TABLE_SCHEMA, TABLE_NAME";
18 |
19 | using var command = new SqlCommand(sql, connection);
20 | using var reader = await command.ExecuteReaderAsync();
21 |
22 | while (await reader.ReadAsync())
23 | {
24 | string schema = reader.GetString(0);
25 | string tableName = reader.GetString(1);
26 | tables.Add($"{schema}.{tableName}");
27 | }
28 |
29 | return tables;
30 | }
31 |
32 | public async Task<List<string>> GetColumnsAsync(SqlConnection connection, string tableName)
33 | {
34 | var columns = new List<string>();
35 |
36 | string schema = "dbo"; // default value
37 | if (tableName.Contains("."))
38 | {
39 | var parts = tableName.Split('.');
40 | schema = parts[0];
41 | tableName = parts[1];
42 | }
43 |
44 | string sql = @"
45 | SELECT
46 | COLUMN_NAME,
47 | DATA_TYPE,
48 | CHARACTER_MAXIMUM_LENGTH,
49 | IS_NULLABLE,
50 | COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY,
51 | (
52 | SELECT COUNT(*)
53 | FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
54 | JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
55 | ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
56 | WHERE kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
57 | AND kcu.TABLE_NAME = c.TABLE_NAME
58 | AND kcu.COLUMN_NAME = c.COLUMN_NAME
59 | AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
60 | ) as IS_PRIMARY_KEY
61 | FROM
62 | INFORMATION_SCHEMA.COLUMNS c
63 | WHERE
64 | TABLE_SCHEMA = @schema
65 | AND TABLE_NAME = @tableName
66 | ORDER BY
67 | ORDINAL_POSITION";
68 |
69 | using var command = new SqlCommand(sql, connection);
70 | command.Parameters.AddWithValue("@schema", schema);
71 | command.Parameters.AddWithValue("@tableName", tableName);
72 |
73 | using var reader = await command.ExecuteReaderAsync();
74 |
75 | while (await reader.ReadAsync())
76 | {
77 | string columnName = reader.GetString(0);
78 | string dataType = reader.GetString(1);
79 | object charMaxLength = reader.GetValue(2);
80 | string isNullable = reader.GetString(3);
81 | int isIdentity = reader.GetInt32(4);
82 | int isPrimaryKey = reader.GetInt32(5);
83 |
84 | string lengthInfo = charMaxLength == DBNull.Value ? "" : $"({charMaxLength})";
85 | string nullableInfo = isNullable == "YES" ? "NULL" : "NOT NULL";
86 | string identityInfo = isIdentity == 1 ? "IDENTITY" : "";
87 | string pkInfo = isPrimaryKey == 1 ? "PRIMARY KEY" : "";
88 |
89 | columns.Add($"{columnName} | {dataType}{lengthInfo} | {nullableInfo} {identityInfo} {pkInfo}".Trim());
90 | }
91 |
92 | return columns;
93 | }
94 |
95 | public async Task<List<string>> GetRelationshipsAsync(SqlConnection connection)
96 | {
97 | var relationships = new List<string>();
98 |
99 | string sql = @"
100 | SELECT
101 | fk.name AS ForeignKey,
102 | OBJECT_NAME(fk.parent_object_id) AS TableName,
103 | COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ColumnName,
104 | OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName,
105 | COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumnName
106 | FROM
107 | sys.foreign_keys AS fk
108 | INNER JOIN
109 | sys.foreign_key_columns AS fkc
110 | ON fk.OBJECT_ID = fkc.constraint_object_id
111 | ORDER BY
112 | TableName, ReferencedTableName";
113 |
114 | using var command = new SqlCommand(sql, connection);
115 | using var reader = await command.ExecuteReaderAsync();
116 |
117 | while (await reader.ReadAsync())
118 | {
119 | string foreignKey = reader.GetString(0);
120 | string tableName = reader.GetString(1);
121 | string columnName = reader.GetString(2);
122 | string referencedTableName = reader.GetString(3);
123 | string referencedColumnName = reader.GetString(4);
124 |
125 | relationships.Add($"{tableName}.{columnName} -> {referencedTableName}.{referencedColumnName} (FK: {foreignKey})");
126 | }
127 |
128 | return relationships;
129 | }
130 |
131 |
132 |
133 | public string ExtractTableName(string query)
134 | {
135 |
136 | var words = query.Split(new[] { ' ', '\t', '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
137 | for (int i = 0; i < words.Length; i++)
138 | {
139 | if ((words[i].ToLower() == "tabla" || words[i].ToLower() == "table") && i + 1 < words.Length)
140 | {
141 | return words[i + 1].Trim(',', '.', ':', ';', '?', '!');
142 | }
143 | else
144 | {
145 | return words[i].Trim(',', '.', ':', ';', '?', '!');
146 | }
147 | }
148 | return string.Empty;
149 | }
150 |
151 | public async Task<List<string>> GetStoreProcedureAsync(SqlConnection connection, string spName)
152 | {
153 | var procedures = new List<string>();
154 |
155 | string sql = @"select name, object_definition(object_id)
156 | from sys.procedures
157 | where name = @spName;";
158 |
159 | using var command = new SqlCommand(sql, connection);
160 | command.Parameters.AddWithValue("@spName", spName);
161 | using var reader = await command.ExecuteReaderAsync();
162 |
163 | while (await reader.ReadAsync())
164 | {
165 | string name = reader.GetString(0);
166 | string definition = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
167 | procedures.Add($"{name}:\n{definition}");
168 | }
169 |
170 | return procedures;
171 | }
172 |
173 | }
174 | }
175 |
```
--------------------------------------------------------------------------------
/SchemaTool.cs:
--------------------------------------------------------------------------------
```csharp
1 | using System.ComponentModel;
2 | using MsSqlMCP.Helpers;
3 | using ModelContextProtocol.Server;
4 | using Microsoft.Data.SqlClient;
5 | using MsSqlMCP;
6 | using System.Text;
7 |
8 | [McpServerToolType]
9 | public static class SchemaTool
10 | {
11 | // Helper method to sanitize database name to prevent SQL injection.
12 | // For SQL Server, names can be enclosed in []. This also escapes any existing ] characters.
13 | private static string SanitizeDatabaseName(string databaseName)
14 | {
15 | return $"[{databaseName.Replace("]", "]]")}]";
16 | }
17 |
18 | [McpServerTool, Description("Get tables name of database. Optionally, specify a database name to query a different database in the same instance.")]
19 | public async static Task<string> GetTables(string? databaseName = null)
20 | {
21 | var (connectionString, warning) = Program.GetConnectionString();
22 | using var connection = new SqlConnection(connectionString);
23 | await connection.OpenAsync();
24 |
25 | if (!string.IsNullOrWhiteSpace(databaseName))
26 | {
27 | using var useDbCommand = new SqlCommand($"USE {SanitizeDatabaseName(databaseName)};", connection);
28 | await useDbCommand.ExecuteNonQueryAsync();
29 | }
30 |
31 | SchemaHelper schemaHelper = new SchemaHelper();
32 | var tables = await schemaHelper.GetTablesAsync(connection);
33 | string dbInfo = string.IsNullOrWhiteSpace(databaseName) ? string.Empty : $" in database '{databaseName}'";
34 | return $"Tables{dbInfo}:\n\n{string.Join("\n", tables)}";
35 | }
36 |
37 | [McpServerTool, Description("Get the columns (fields) of a database table. Optionally, specify a database name to query a different database in the same instance.")]
38 | public async static Task<string> GetColumns(string tableName, string? databaseName = null)
39 | {
40 | var (connectionString, warning) = Program.GetConnectionString();
41 | using var connection = new SqlConnection(connectionString);
42 | await connection.OpenAsync();
43 |
44 | if (!string.IsNullOrWhiteSpace(databaseName))
45 | {
46 | using var useDbCommand = new SqlCommand($"USE {SanitizeDatabaseName(databaseName)};", connection);
47 | await useDbCommand.ExecuteNonQueryAsync();
48 | }
49 |
50 | SchemaHelper schemaHelper = new SchemaHelper();
51 | tableName = schemaHelper.ExtractTableName(tableName);
52 | if (!string.IsNullOrEmpty(tableName))
53 | {
54 | var columns = await schemaHelper.GetColumnsAsync(connection, tableName);
55 | string dbInfo = string.IsNullOrWhiteSpace(databaseName) ? string.Empty : $" (database '{databaseName}')";
56 | return $"Columns in the table {tableName}{dbInfo}:\n\n{string.Join("\n", columns)}";
57 | }
58 | else
59 | {
60 | return "Please specify the table name to query its fields.";
61 | }
62 | }
63 |
64 | [McpServerTool, Description("Get the relationships between tables in the database. Optionally, specify a database name to query a different database in the same instance.")]
65 | public async static Task<string> GetRelationships(string? databaseName = null)
66 | {
67 | var (connectionString, warning) = Program.GetConnectionString();
68 | using var connection = new SqlConnection(connectionString);
69 | await connection.OpenAsync();
70 |
71 | if (!string.IsNullOrWhiteSpace(databaseName))
72 | {
73 | using var useDbCommand = new SqlCommand($"USE {SanitizeDatabaseName(databaseName)};", connection);
74 | await useDbCommand.ExecuteNonQueryAsync();
75 | }
76 |
77 | SchemaHelper schemaHelper = new SchemaHelper();
78 | var relationships = await schemaHelper.GetRelationshipsAsync(connection);
79 | string dbInfo = string.IsNullOrWhiteSpace(databaseName) ? string.Empty : $" in database '{databaseName}'";
80 | return $"Relationships between tables{dbInfo}:\n\n{string.Join("\n", relationships)}";
81 | }
82 |
83 | [McpServerTool, Description("Execute a SQL query against the database. Only allows SELECT statements and other read-only operations. Optionally, specify a database name to query a different database in the same instance.")]
84 | public async static Task<string> ExecuteSql(string sqlQuery, string? databaseName = null)
85 | {
86 | if (string.IsNullOrWhiteSpace(sqlQuery))
87 | {
88 | return "SQL query cannot be empty.";
89 | }
90 |
91 | // Block any statements that can modify data or structure
92 | string normalizedQuery = sqlQuery.Trim().ToUpperInvariant();
93 | string[] forbiddenStatements = {
94 | "DROP ", "INSERT ", "UPDATE ", "DELETE ", "TRUNCATE ", "ALTER ", "CREATE ",
95 | "EXEC ", "EXECUTE ", "MERGE ", "BULK ", "BACKUP ", "RESTORE ", "DBCC ",
96 | "GRANT ", "DENY ", "REVOKE ", "USE ", "SHUTDOWN ", "KILL ", "RECONFIGURE "
97 | };
98 |
99 | foreach (string forbidden in forbiddenStatements)
100 | {
101 | if (normalizedQuery.StartsWith(forbidden))
102 | {
103 | return $"Error: {forbidden.Trim()} statements are not allowed. Only read-only operations are permitted.";
104 | }
105 | }
106 |
107 | // Additional check for multi-statement queries that might contain forbidden operations
108 | if (normalizedQuery.Contains(";"))
109 | {
110 | string[] statements = sqlQuery.Split(';', StringSplitOptions.RemoveEmptyEntries);
111 | foreach (string statement in statements)
112 | {
113 | string normalizedStatement = statement.Trim().ToUpperInvariant();
114 | if (string.IsNullOrWhiteSpace(normalizedStatement)) continue;
115 |
116 | foreach (string forbidden in forbiddenStatements)
117 | {
118 | if (normalizedStatement.StartsWith(forbidden))
119 | {
120 | return $"Error: {forbidden.Trim()} statements are not allowed in multi-statement queries. Only read-only operations are permitted.";
121 | }
122 | }
123 | }
124 | }
125 |
126 | var (connectionString, warningFlag) = Program.GetConnectionString();
127 | if (warningFlag)
128 | {
129 | // Potentially log the warning or handle it as needed
130 | }
131 |
132 | StringBuilder resultBuilder = new StringBuilder();
133 |
134 | try
135 | {
136 | using var connection = new SqlConnection(connectionString);
137 | await connection.OpenAsync();
138 |
139 | if (!string.IsNullOrWhiteSpace(databaseName))
140 | {
141 | using var useDbCommand = new SqlCommand($"USE {SanitizeDatabaseName(databaseName)};", connection);
142 | await useDbCommand.ExecuteNonQueryAsync();
143 | resultBuilder.AppendLine($"Switched to database '{SanitizeDatabaseName(databaseName)}'."); // Show sanitized name for clarity
144 | }
145 |
146 | using var command = new SqlCommand(sqlQuery, connection);
147 | bool isSelectQuery = sqlQuery.Trim().ToUpperInvariant().StartsWith("SELECT");
148 |
149 | if (isSelectQuery)
150 | {
151 | using var reader = await command.ExecuteReaderAsync();
152 | if (reader.HasRows)
153 | {
154 | for (int i = 0; i < reader.FieldCount; i++)
155 | {
156 | resultBuilder.Append(reader.GetName(i));
157 | if (i < reader.FieldCount - 1) resultBuilder.Append("\t|\t");
158 | }
159 | resultBuilder.AppendLine();
160 | // Ensure separator line is not drawn if resultBuilder is empty or too short
161 | if (resultBuilder.Length > 2)
162 | {
163 | resultBuilder.AppendLine(new string('-', resultBuilder.Length - (resultBuilder.ToString().EndsWith(Environment.NewLine) ? Environment.NewLine.Length*2 : Environment.NewLine.Length) ));
164 | } else if (resultBuilder.Length > 0 && !resultBuilder.ToString().EndsWith(Environment.NewLine)) {
165 | resultBuilder.AppendLine(new string('-', resultBuilder.Length));
166 | }
167 |
168 | while (await reader.ReadAsync())
169 | {
170 | for (int i = 0; i < reader.FieldCount; i++)
171 | {
172 | resultBuilder.Append(reader[i]?.ToString() ?? "NULL");
173 | if (i < reader.FieldCount - 1) resultBuilder.Append("\t|\t");
174 | }
175 | resultBuilder.AppendLine();
176 | }
177 | }
178 | else
179 | {
180 | resultBuilder.AppendLine("No rows returned from the query.");
181 | }
182 | }
183 | else
184 | {
185 | int affectedRows = await command.ExecuteNonQueryAsync();
186 | resultBuilder.AppendLine($"Command executed successfully. {affectedRows} row(s) affected.");
187 | }
188 | }
189 | catch (SqlException ex)
190 | {
191 | return $"SQL Error: {ex.Message}";
192 | }
193 | catch (Exception ex)
194 | {
195 | return $"Error executing SQL query: {ex.Message}";
196 | }
197 |
198 | return resultBuilder.ToString();
199 | }
200 |
201 | [McpServerTool, Description("Get the definition of a stored procedure by name. Optionally, specify a database name to query a different database in the same instance.")]
202 | public async static Task<string> GetStoreProcedure(string spName, string? databaseName = null)
203 | {
204 | if (string.IsNullOrWhiteSpace(spName))
205 | {
206 | return "Stored procedure name cannot be empty.";
207 | }
208 |
209 | var (connectionString, warning) = Program.GetConnectionString();
210 | using var connection = new SqlConnection(connectionString);
211 | await connection.OpenAsync();
212 |
213 | if (!string.IsNullOrWhiteSpace(databaseName))
214 | {
215 | using var useDbCommand = new SqlCommand($"USE {SanitizeDatabaseName(databaseName)};", connection);
216 | await useDbCommand.ExecuteNonQueryAsync();
217 | }
218 |
219 | SchemaHelper schemaHelper = new SchemaHelper();
220 | var procedures = await schemaHelper.GetStoreProcedureAsync(connection, spName);
221 | string dbInfo = string.IsNullOrWhiteSpace(databaseName) ? string.Empty : $" in database '{databaseName}'";
222 | if (procedures.Count == 0)
223 | {
224 | return $"Stored procedure '{spName}' not found{dbInfo}.";
225 | }
226 | return $"Stored procedure '{spName}'{dbInfo}:\n\n{string.Join("\n\n", procedures)}";
227 | }
228 | }
229 |
```