Schema Design Guidance
Use this skill when the user asks about schema design patterns, computed columns, full-text indexes, indexed columns, or row counts.
Required tools
- A SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP server that ships with the
microsoft-sql-adminplugin (mssql_list_database_connections,mssql_connect_database, thenmssql_execute_query); any other SQL tool the agent host provides for running T-SQL and reading result sets;sqlcmdfrom a terminal. It is used to run catalog queries and validate generated DDL, and to execute DDL only when the user orders the task. If the SQL Tools MCP server is not connected or lists no connections, follow thesql-tools-mcp-setupskill, which asks before installing or configuring anything. If no SQL tool can be made available, give the user the T-SQL to run and ask for the results.
Validating DDL
Validate generated DDL without executing it by sending the SET statements and the script in one request, each SET in its own batch:
SET NOEXEC ON;
GO
<DDL script>
GO
SET NOEXEC OFF;
GO
SET NOEXEC ON compiles and resolves names but executes nothing (use SET PARSEONLY ON / OFF for a syntax-only check). Objects created earlier in the same script do not exist yet under NOEXEC, so later references to them report errors. Never send the script in a separate request on the assumption that NOEXEC is still on: if the tool does not keep the session, the DDL would execute.
Computed Columns
If the user asks for non-persisted computed columns, do NOT include PERSISTED in the DDL statement.
Finding Full-Text Indexes
The column is_full_text_enabled is on sys.databases (not sys.tables) and indicates full text is enabled on the database. To find tables with full-text indexes:
SELECT
t.name AS TableName,
s.name AS SchemaName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.object_id IN (
SELECT object_id FROM sys.fulltext_indexes
)
OPTION (MAXDOP 1);
Searching for Indexed Columns
Use sys.index_columns, not sys.columns:
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
ind.name AS IndexName,
col.name AS ColumnName,
ind.type_desc AS IndexType,
ind.is_unique AS IsUnique,
ind.is_primary_key AS IsPrimaryKey,
ind.is_unique_constraint AS IsUniqueConstraint,
ic.is_included_column AS IsIncludedColumn,
ic.key_ordinal AS KeyOrdinal
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
ORDER BY SchemaName, TableName, IndexName, ind.index_id, ic.is_included_column, ic.key_ordinal
OPTION (MAXDOP 1);
Only if no records are found with the above query, look up the objects and columns the user referred to by name:
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS ObjectName,
o.type_desc AS ObjectType,
c.name AS ColumnName,
TYPE_NAME(c.user_type_id) AS DataType
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON c.object_id = o.object_id
WHERE o.is_ms_shipped = 0
AND (o.name LIKE N'%<name>%' OR c.name LIKE N'%<name>%') -- agent substitutes the search term
ORDER BY SchemaName, ObjectName, c.column_id
OPTION (MAXDOP 1);
Row Counting
IMPORTANT:
- Always check that the table exists before counting rows.
- DO NOT use
RowCountas an alias — it's a reserved keyword. - Use
TotalRowsinstead.
-- Good: Row count for all tables (default TOP 10)
SELECT TOP 10
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
SUM(p.rows) AS TotalRows
FROM sys.tables AS t
JOIN sys.partitions AS p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY t.schema_id, t.name
ORDER BY TotalRows DESC
OPTION (MAXDOP 1);
- Use
sys.tables+sys.partitions, NOTINFORMATION_SCHEMA. - Default to TOP 10 unless the user specifies otherwise.
Behavioral Guidance
"How do I..." Questions
If the user asks how to do something, educate them — do NOT execute the task. Match the level of generality to the question.
Task Execution
If the user orders task completion, execute it on their behalf.
Database Object Characteristics
If the user asks about characteristics of database objects, first check if the objects exist before querying them.
Schema Alteration
When creating ALTER scripts:
- Find the correct schema for objects (users often omit it)
- Preserve existing constraints unless asked to modify them
- Understand dependent object relationships
- Validate as described in Validating DDL — do it automatically, don't ask