CoPilot inside SSMS (not a Fabric databse)

Question

CoPilot inside SSMS (not a Fabric databse)

Rose, Lance 0

from inside SSMs, what CoPilot assistance or options do I have Could I for instance, ask for a T-sql snippet to search through the current database table names for a given string?

0 comments

2 answers

Your answer

Answer 1

Deepesh Dhake 1,325

Yes. In SSMS 22 (latest is 22.11.3), Copilot Free works:

Ask mode chat: knows your connected database. Asking for a table-name search snippet works fine.

Agent Mode (now GA): can query and work with Object Explorer objects.

Slash commands: /doc, /explain, /fix, /optimize.

Autocompletions: off by default, so turn them on if you want them.

set Copilot's execution context before using Agent Mode.

SELECT s.name, t.name
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name LIKE N'%Audit%';

0 comments

Answer 2

GitHub Copilot is available directly in SSMS 22 or later and can generate a T-SQL query that searches table names in the database connected to the active query editor; it is not limited to Fabric databases.^1^

Set up Copilot in SSMS

Install or upgrade to SSMS 22 or later.
Connect to SQL Server and open a query window for the intended database.
Select the GitHub Copilot badge in the upper-right corner.
If necessary, select Install Copilot. Alternatively, modify the SSMS installation in Visual Studio Installer and add the AI Assistance workload.
Select Open Chat Window to Sign In, then sign in using a GitHub account with Copilot access. A Copilot Free option is available from the sign-in flow.^1^

The active query editor’s database connection is included as implicit context, so open Copilot from a query window connected to the database you want it to consider.^2^

Ask for the table-name query

Enter a prompt such as:

Write a T-SQL query that searches all user table names in the current database for a supplied string. Return the schema and table name, ordered by schema and table.

A suitable result would be:

DECLARE @SearchText nvarchar(128) = N'customer';

SELECT
    s.name AS schema_name,
    t.name AS table_name
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE t.name LIKE N'%' + @SearchText + N'%'
ORDER BY s.name, t.name;

Run it from a query window whose database selector is set to the intended database. SQL Server exposes table and schema metadata through the sys.tables and sys.schemas catalog views.^3^

Metadata visibility follows your SQL permissions: objects may not appear unless your login owns them or has permission on them.^3^

Other assistance available

Copilot in SSMS provides:

Chat and inline chat: Generate, revise, and explain T-SQL; ask database-specific or general SQL questions.
Code completions: Receive suggested T-SQL as gray “ghost text” while typing.
Next Edit Suggestions: Predict likely subsequent changes after an edit.
Database instructions: Store database-specific conventions and business rules to improve responses.^1^
Database exploration: Ask about databases, configuration, compatibility level, table sizes, connections, SQL Agent failures, open transactions, and similar administrative topics.^4^
Selected-code operations: Highlight T-SQL and use /explain, /fix, /optimize, or /doc in chat.^2^

For inline completions, use Tools → Options → Text Editor → Inline Suggestions → General and enable them. This feature requires SSMS 22.2 or later, the AI Assistance workload, and a GitHub account with Copilot access.^5^

Review generated scripts before executing them, particularly DDL, security, backup, and data-modification commands. Queries run using the connected user’s login and permissions unless database instructions specify a custom database user or SQL login.^6^

References