SQL Server Skill | Agent Skills

SQL Server

Microsoft SQL Server management — queries, schema inspection, inserts, updates, and performance monitoring.

Setup

export SQLCMDSERVER="localhost"
export SQLCMDUSER="sa"
export SQLCMDPASSWORD="yourpassword"   # Avoids passwords in shell history
export SQLCMDDBNAME="mydb"

Connect using sqlcmd:

sqlcmd -S "$SQLCMDSERVER" -U "$SQLCMDUSER" -P "$SQLCMDPASSWORD" -d "$SQLCMDDBNAME"
# or inline:
sqlcmd -S localhost -U sa -P pass -d mydb
# or with explicit port:
sqlcmd -S "tcp:myserver.database.windows.net,1433" -U user -P pass -d mydb

Run a single query without entering the interactive shell:

sqlcmd -S localhost -U sa -P pass -d mydb -Q "SELECT TOP 10 * FROM Orders"

Run a SQL script file:

sqlcmd -S localhost -U sa -P pass -d mydb -i script.sql

Installation

# Linux (Debian/Ubuntu) — mssql-tools
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list \
  | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# mssql-cli (interactive alternative with autocomplete)
pip install mssql-cli

Essential sqlcmd Options

| Option | Description | |---|---| | -S | Server (host or host,port or tcp:host,port) | | -U | Username | | -P | Password (prefer SQLCMDPASSWORD env var) | | -d | Database | | -E | Use Windows integrated authentication | | -Q | Run query and exit | | -q | Run query, stay in interactive mode | | -i | Input SQL file | | -o | Output file | | -h -1 | Remove column headers | | -s "," | Set column separator (e.g. for CSV output) | | -W | Remove trailing spaces from columns |

Common Operations

Query

SELECT TOP 10 * FROM Users;
SELECT column1, column2 FROM TableName WHERE condition ORDER BY column1 DESC;

Insert / Update / Delete

INSERT INTO Users (Name, Email) VALUES ('Alice', 'alice@example.com');
UPDATE Users SET Email = 'new@example.com' WHERE Id = 1;
DELETE FROM Users WHERE Id = 1;

Schema Inspection

-- List all tables in current database
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Describe a table (columns, types, nullability)
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders'
ORDER BY ORDINAL_POSITION;

-- List indexes on a table
SELECT i.name AS index_name, i.type_desc, c.name AS column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = 'Orders';

-- List foreign keys
SELECT
  fk.name AS fk_name,
  OBJECT_NAME(fk.parent_object_id) AS parent_table,
  c.name AS parent_column,
  OBJECT_NAME(fk.referenced_object_id) AS ref_table,
  rc.name AS ref_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id;

Schema Changes

-- Create table
CREATE TABLE Orders (
  Id        INT IDENTITY(1,1) PRIMARY KEY,
  UserId    INT NOT NULL REFERENCES Users(Id),
  Total     DECIMAL(10,2),
  CreatedAt DATETIME2 DEFAULT SYSDATETIME()
);

-- Add / drop column
ALTER TABLE Orders ADD Status NVARCHAR(50) DEFAULT 'pending';
ALTER TABLE Orders DROP COLUMN Status;

-- Create index
CREATE INDEX IX_Orders_UserId ON Orders(UserId);
-- Non-clustered covering index:
CREATE NONCLUSTERED INDEX IX_Orders_Status ON Orders(Status) INCLUDE (Total, CreatedAt);

Transactions

BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
  UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;
COMMIT;
-- or ROLLBACK; to undo

Performance & Monitoring

-- Currently running queries
SELECT
  r.session_id,
  r.status,
  r.cpu_time,
  r.total_elapsed_time,
  r.logical_reads,
  t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id != @@SPID;

-- Top CPU-consuming queries (from plan cache)
SELECT TOP 10
  qs.total_worker_time / qs.execution_count AS avg_cpu_time,
  qs.execution_count,
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_time DESC;

-- Active connections
SELECT session_id, login_name, status, host_name, program_name, database_id, cpu_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- Table sizes
SELECT
  t.NAME AS table_name,
  p.rows AS row_count,
  SUM(a.total_pages) * 8 AS total_kb,
  SUM(a.used_pages) * 8 AS used_kb
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.NAME, p.rows
ORDER BY total_kb DESC;

-- Explain query plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Orders WHERE UserId = 42;

-- Or view estimated plan without running (SSMS/Azure Data Studio syntax)
-- Prefix query with: SET SHOWPLAN_TEXT ON; GO

Backup & Restore

# Backup (T-SQL via sqlcmd)
sqlcmd -S localhost -U sa -P pass -Q \
  "BACKUP DATABASE [mydb] TO DISK = N'/var/opt/mssql/backup/mydb.bak' WITH NOFORMAT, INIT, STATS=10"

# Restore
sqlcmd -S localhost -U sa -P pass -Q \
  "RESTORE DATABASE [mydb] FROM DISK = N'/var/opt/mssql/backup/mydb.bak' WITH REPLACE, STATS=10"

Bulk Import/Export (BCP)

# Export table to CSV
bcp mydb.dbo.Orders out orders.csv -S localhost -U sa -P pass -c -t ','

# Import CSV into table
bcp mydb.dbo.Orders in orders.csv -S localhost -U sa -P pass -c -t ','

# Export query result to CSV
bcp "SELECT * FROM mydb.dbo.Orders WHERE Status = 'pending'" queryout pending.csv \
  -S localhost -U sa -P pass -c -t ','

Safety Rules

Always confirm before running DELETE, DROP, or TRUNCATE
Always backup before schema migrations
Use transactions for multi-step data changes
Use SET STATISTICS IO ON / execution plans to preview query cost before running on large tables
Prefer targeted indexes over broad ones — over-indexing slows writes
Use SQLCMDPASSWORD environment variable instead of -P flag to keep passwords out of shell history

Reference

Full sqlcmd documentation: https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility

Agent Skills: SQL Server

Install this agent skill to your local

Skill Files