Sqldw Consumption Cli

Update Check — ONCE PER SESSION (mandatory)

The first time this skill is used in a session, run the check-updates skill before proceeding.

- GitHub Copilot CLI / VS Code: invoke the check-updates skill.

- Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.

- Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering

2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

SQL Endpoint Consumption — CLI Skill

Table of Contents

---

Tool Stack

Agent check — verify before first SQL operation:

```bash

sqlcmd --version 2>/dev/null || echo "INSTALL: winget install sqlcmd OR brew install sqlcmd"

```

---

Connection

Discover the SQL Endpoint FQDN

Per COMMON-CLI.md Discovering Connection Parameters via REST:

WS_ID="<workspaceId>"
ITEM_ID="<warehouseOrLakehouseId>"

# Warehouse
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/warehouses/$ITEM_ID" \
  --query "properties.connectionString" --output tsv

# Lakehouse SQL endpoint
az rest --method get \
  --resource "https://api.fabric.microsoft.com" \
  --url "https://api.fabric.microsoft.com/v1/workspaces/$WS_ID/lakehouses/$ITEM_ID" \
  --query "properties.sqlEndpointProperties.connectionString" --output tsv

Result: <uniqueId>.datawarehouse.fabric.microsoft.com

Connect with sqlcmd (Go)

# Interactive session (Entra login via browser if needed)
sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DatabaseName>" -G

# Non-interactive one-shot query
sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DatabaseName>" -G \
  -Q "SELECT TOP 10 * FROM dbo.FactSales"

# Explicit ActiveDirectoryDefault (uses az login session)
sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DatabaseName>" \
  --authentication-method ActiveDirectoryDefault \
  -Q "SELECT TOP 10 * FROM dbo.FactSales"

# Service principal (CI/CD)
SQLCMDPASSWORD="<clientSecret>" \
sqlcmd -S "<endpoint>.datawarehouse.fabric.microsoft.com" -d "<DatabaseName>" \
  --authentication-method ActiveDirectoryServicePrincipal \
  -U "<appId>" \
  -Q "SELECT COUNT(*) FROM dbo.FactSales"

Reusable Connection Variables

# Set once at script top
FABRIC_SERVER="<endpoint>.datawarehouse.fabric.microsoft.com"
FABRIC_DB="<DatabaseName>"
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

# Use throughout
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.DimProduct"
$SQLCMD -i myscript.sql

PowerShell / Windows CMD

# PowerShell
$s = "<endpoint>.datawarehouse.fabric.microsoft.com"; $db = "<DatabaseName>"
sqlcmd -S $s -d $db -G -Q "SELECT TOP 10 * FROM dbo.FactSales"
# CMD: use set S=... and %S% / %DB% instead of $variables

---

Agentic Exploration ("Chat With My Data")

Schema Discovery Sequence

Run these in order to understand what's in the endpoint. See references/discovery-queries.md for extended discovery queries.

# 1. List schemas
$SQLCMD -Q "SELECT schema_name FROM information_schema.schemata ORDER BY schema_name" -W

# 2. List tables and views
$SQLCMD -Q "SELECT table_schema, table_name, table_type FROM information_schema.tables ORDER BY table_schema, table_name" -W

# 3. Columns for a table
$SQLCMD -Q "SELECT column_name, data_type, character_maximum_length, is_nullable FROM information_schema.columns WHERE table_schema='dbo' AND table_name='FactSales' ORDER BY ordinal_position" -W

# 4. Preview rows
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.FactSales" -W

# 5. Row counts
$SQLCMD -Q "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS row_count FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY row_count DESC" -W

# 6. Programmability objects (views, functions, procedures)
$SQLCMD -Q "SELECT name, type_desc FROM sys.objects WHERE type IN ('V','FN','IF','P','TF') ORDER BY type_desc, name" -W

Agentic Workflow

1. Discover → Run Steps 1–3 to understand available tables/columns. 2. Sample → SELECT TOP 5 on relevant tables. 3. Formulate → Write T-SQL using SQLDW-CONSUMPTION-CORE.md Supported T-SQL Surface Area. 4. Execute → $SQLCMD -Q "...". 5. Iterate → Refine based on results. 6. Present → Show results or generate a reusable script (Script Generation section).

---

Gotchas, Rules, Troubleshooting

For full T-SQL/platform gotchas: SQLDW-CONSUMPTION-CORE.md Gotchas and Troubleshooting Reference and COMMON-CLI.md Gotchas & Troubleshooting (CLI-Specific).

MUST DO

• Always `-d <DatabaseName>` — FQDN alone is insufficient.
• Always `-G` or `--authentication-method` — SQL auth not supported on Fabric.
• `az login` first — ActiveDirectoryDefault uses az session. No session → cryptic failure.
• `SET NOCOUNT ON;` in scripts — suppresses row-count messages that corrupt output.
• Label queries with OPTION (LABEL = 'AGENTCLI_...') for Query Insights tracing.

AVOID

• ODBC sqlcmd (/opt/mssql-tools/bin/sqlcmd) — requires ODBC driver. Use Go version.
• Omitting `-W` in scripts — trailing spaces corrupt CSV.
• DML on SQLEP — Lakehouse/Mirrored DB endpoints are read-only. DML only on Warehouse.
• MARS — not supported. Remove MultipleActiveResultSets from connection strings.
• Hardcoded FQDNs — discover via REST API (Discover the SQL Endpoint FQDN).

PREFER

• `sqlcmd (Go) -G` over curl+token for SQL queries.
• `-Q` (non-interactive exit) for agentic use.
• Piped input for multi-statement batches or queries with quotes.
• `-i file.sql` for complex queries — avoids shell escaping.
• `-F vertical` for exploration of wide tables.
• Env vars (FABRIC_SERVER, FABRIC_DB) for script reuse.
• `az rest` for Fabric REST API — use sqlcmd only for T-SQL.

TROUBLESHOOTING

Consumption CLI Quick Reference

Concise sqlcmd output formatting, monitoring queries, and agent tips. For full T-SQL patterns, see SQLDW-CONSUMPTION-CORE.md. For full reusable scripts, see script-templates.md.

All examples assume reusable connection variables are set:

FABRIC_SERVER="<endpoint>.datawarehouse.fabric.microsoft.com"
FABRIC_DB="<WarehouseName>"
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

Script Generation

Bash Template

See script-templates.md for full bash and PowerShell templates.

Key pattern for script generation:

#!/usr/bin/env bash
set -euo pipefail
FABRIC_SERVER="${FABRIC_SERVER:?Set FABRIC_SERVER env var}"
FABRIC_DB="${FABRIC_DB:?Set FABRIC_DB env var}"
command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found. Install: winget install sqlcmd"; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G \
  -Q "SET NOCOUNT ON; SELECT * FROM dbo.FactSales WHERE SaleDate >= '2025-01-01'" \
  -W -s"," -h-1 -o results.csv
echo "Results written to results.csv"

sqlcmd Output Formatting Flags

Piped Input

# Pipe SQL from stdin
echo "SELECT TOP 5 * FROM dbo.FactSales" | sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G

# Here-doc for multi-statement
cat <<'SQL' | sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G
SET NOCOUNT ON;
SELECT COUNT(*) AS TotalRows FROM dbo.FactSales;
SELECT TOP 3 ProductID, SUM(Amount) AS Total FROM dbo.FactSales GROUP BY ProductID ORDER BY Total DESC;
SQL

Parameterized Queries (sqlcmd Variables)

sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G \
  -v StartDate="2025-01-01" EndDate="2025-06-30" \
  -Q "SET NOCOUNT ON; SELECT * FROM dbo.FactSales WHERE SaleDate BETWEEN '$(StartDate)' AND '$(EndDate)'" -W

Monitoring and Performance

For full query catalog see SQLDW-CONSUMPTION-CORE.md § Monitoring and Diagnostics and script-templates.md § Performance Investigation.

# Active queries
$SQLCMD -Q "SELECT request_id, session_id, command, status, total_elapsed_time/1000 AS elapsed_sec FROM sys.dm_exec_requests WHERE status='running' ORDER BY total_elapsed_time DESC" -W

# Kill a stuck session (Admin role)
$SQLCMD -Q "KILL '<distributed_statement_id>'"

Agent Integration Notes

• GitHub Copilot CLI: gh copilot suggest -t shell for sqlcmd one-liners; ensure -G and -d in output; use "explain" mode for errors.
• Claude Code / Cowork: run sqlcmd -Q "..." via bash tool; follow the Agentic Workflow in SKILL.md; produce scripts using script-templates.md.
• Always verify sqlcmd availability before first SQL operation.

Extended Discovery Queries

Queries for deep schema exploration beyond the basics in SKILL.md Schema Discovery Sequence. All queries work on Warehouse, Lakehouse SQLEP, and Mirrored DB SQLEP.

Schema and Object Discovery

Table and Column Metadata

# All columns across all tables with types
$SQLCMD -Q "
SELECT t.table_schema, t.table_name, c.column_name,
       c.data_type, c.character_maximum_length,
       c.numeric_precision, c.numeric_scale, c.is_nullable
FROM information_schema.tables t
JOIN information_schema.columns c
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE t.table_type = 'BASE TABLE'
ORDER BY t.table_schema, t.table_name, c.ordinal_position" -W

# Tables with row counts and column counts
$SQLCMD -Q "
SELECT s.name AS [schema], t.name AS [table],
       COUNT(DISTINCT c.column_id) AS col_count,
       SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
GROUP BY s.name, t.name
ORDER BY row_count DESC" -W

# Constraints (PK, FK, UNIQUE)
$SQLCMD -Q "
SELECT tc.constraint_type, tc.table_schema, tc.table_name,
       tc.constraint_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
ORDER BY tc.table_schema, tc.table_name, tc.constraint_type" -W

# Foreign key relationships (useful for JOIN hints)
$SQLCMD -Q "
SELECT
    fk.name AS fk_name,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' + OBJECT_NAME(fk.parent_object_id) AS child_table,
    COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS child_column,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) AS parent_table,
    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS parent_column
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
ORDER BY child_table, fk_name" -W

View and Function Definitions

# View definitions (source SQL)
$SQLCMD -Q "
SELECT s.name AS [schema], v.name AS [view],
       m.definition
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
JOIN sys.sql_modules m ON v.object_id = m.object_id
ORDER BY s.name, v.name" -W

# Function definitions
$SQLCMD -Q "
SELECT s.name AS [schema], o.name AS [function], o.type_desc,
       m.definition
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type IN ('FN','IF','TF')
ORDER BY s.name, o.name" -W

# Stored procedure definitions
$SQLCMD -Q "
SELECT s.name AS [schema], p.name AS [procedure],
       m.definition
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id
JOIN sys.sql_modules m ON p.object_id = m.object_id
ORDER BY s.name, p.name" -W

# Procedure parameters
$SQLCMD -Q "
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [schema],
       OBJECT_NAME(p.object_id) AS [procedure],
       p.name AS param_name,
       TYPE_NAME(p.user_type_id) AS data_type,
       p.max_length, p.is_output
FROM sys.parameters p
JOIN sys.procedures pr ON p.object_id = pr.object_id
WHERE p.parameter_id > 0
ORDER BY [schema], [procedure], p.parameter_id" -W

Cross-Database Discovery

# List all accessible databases in the workspace
$SQLCMD -Q "SELECT name, create_date FROM sys.databases ORDER BY name" -W

# Tables in another database (3-part name)
$SQLCMD -Q "SELECT table_schema, table_name FROM OtherDatabase.information_schema.tables ORDER BY table_schema, table_name" -W

Security Discovery

# Current user identity
$SQLCMD -Q "SELECT USER_NAME() AS current_user, SUSER_SNAME() AS login_name" -W

# Database principals
$SQLCMD -Q "
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE type NOT IN ('R')
ORDER BY name" -W

# Role memberships
$SQLCMD -Q "
SELECT r.name AS role_name, m.name AS member_name
FROM sys.database_role_members drm
JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
ORDER BY r.name, m.name" -W

# Object-level permissions
$SQLCMD -Q "
SELECT
    dp.state_desc + ' ' + dp.permission_name AS permission,
    OBJECT_SCHEMA_NAME(dp.major_id) + '.' + OBJECT_NAME(dp.major_id) AS [object],
    prin.name AS grantee
FROM sys.database_permissions dp
JOIN sys.database_principals prin ON dp.grantee_principal_id = prin.principal_id
WHERE dp.major_id > 0
ORDER BY [object], grantee" -W

Statistics and Performance Metadata

# Statistics on tables
$SQLCMD -Q "
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS [schema],
       OBJECT_NAME(s.object_id) AS [table],
       s.name AS stat_name,
       COL_NAME(sc.object_id, sc.column_id) AS column_name,
       STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.stats s
JOIN sys.stats_columns sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
ORDER BY [schema], [table], stat_name" -W

# Data clustering info (DW only) — see SQLDW-CONSUMPTION-CORE.md § Performance for full query
$SQLCMD -Q "SELECT OBJECT_SCHEMA_NAME(ic.object_id) AS [schema], OBJECT_NAME(ic.object_id) AS [table], COL_NAME(ic.object_id, ic.column_id) AS cluster_column FROM sys.index_columns ic JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE i.type_desc = 'CLUSTERED COLUMNSTORE' ORDER BY [schema], [table], ic.key_ordinal" -W

Script Templates

Self-contained templates for generating reusable query scripts.

Bash — Data Export

Query to CSV

#!/usr/bin/env bash
set -euo pipefail

# --- Configuration ---
FABRIC_SERVER="${FABRIC_SERVER:?Set FABRIC_SERVER env var (e.g. xxx.datawarehouse.fabric.microsoft.com)}"
FABRIC_DB="${FABRIC_DB:?Set FABRIC_DB env var (e.g. MyWarehouse)}"
OUTPUT_FILE="${1:-results.csv}"

# --- Prerequisites ---
command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd (Go) not found. Install: winget install sqlcmd OR brew install sqlcmd"; exit 1; }
az account show >/dev/null 2>&1 || { echo "ERROR: Not logged in. Run: az login"; exit 1; }

# --- Query ---
QUERY=$(cat <<'SQL'
SET NOCOUNT ON;
SELECT
    ProductID,
    ProductName,
    Category,
    Price
FROM dbo.DimProduct
ORDER BY ProductName;
SQL
)

echo "$QUERY" | sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G \
  -W -s"," -w 4000 -h 1 \
  -o "$OUTPUT_FILE"

echo "✓ Written to $OUTPUT_FILE ($(wc -l < "$OUTPUT_FILE") lines)"

Parameterized Date Range Export

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}"
FABRIC_DB="${FABRIC_DB:?}"
START_DATE="${1:?Usage: $0 <start_date> <end_date> [output_file]}"
END_DATE="${2:?Usage: $0 <start_date> <end_date> [output_file]}"
OUTPUT_FILE="${3:-export_${START_DATE}_${END_DATE}.csv}"

command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

sqlcmd -S "$FABRIC_SERVER" -d "$FABRIC_DB" -G \
  -v StartDate="$START_DATE" EndDate="$END_DATE" \
  -Q "SET NOCOUNT ON; SELECT * FROM dbo.FactSales WHERE SaleDate BETWEEN '\$(StartDate)' AND '\$(EndDate)'" \
  -W -s"," -w 4000 -h 1 \
  -o "$OUTPUT_FILE"

echo "✓ Exported to $OUTPUT_FILE"

Bash — Schema Discovery Report

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}"
FABRIC_DB="${FABRIC_DB:?}"
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

echo "=== SCHEMAS ==="
$SQLCMD -Q "SELECT schema_name FROM information_schema.schemata ORDER BY schema_name" -W

echo ""
echo "=== TABLES (with row counts) ==="
$SQLCMD -Q "
SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS rows
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
GROUP BY s.name, t.name
ORDER BY rows DESC" -W

echo ""
echo "=== VIEWS ==="
$SQLCMD -Q "SELECT SCHEMA_NAME(schema_id) AS [schema], name FROM sys.views ORDER BY [schema], name" -W

echo ""
echo "=== STORED PROCEDURES ==="
$SQLCMD -Q "SELECT SCHEMA_NAME(schema_id) AS [schema], name FROM sys.procedures ORDER BY [schema], name" -W

echo ""
echo "=== FUNCTIONS ==="
$SQLCMD -Q "SELECT SCHEMA_NAME(schema_id) AS [schema], name, type_desc FROM sys.objects WHERE type IN ('FN','IF','TF') ORDER BY [schema], name" -W

Bash — Performance Investigation

#!/usr/bin/env bash
set -euo pipefail

FABRIC_SERVER="${FABRIC_SERVER:?}"
FABRIC_DB="${FABRIC_DB:?}"
HOURS="${1:-24}"
SQLCMD="sqlcmd -S $FABRIC_SERVER -d $FABRIC_DB -G"

command -v sqlcmd >/dev/null 2>&1 || { echo "ERROR: sqlcmd not found."; exit 1; }
az account show >/dev/null 2>&1 || { echo "Run 'az login' first."; exit 1; }

echo "=== ACTIVE QUERIES ==="
$SQLCMD -Q "SELECT request_id, session_id, command, status, total_elapsed_time/1000 AS elapsed_sec FROM sys.dm_exec_requests WHERE status='running' ORDER BY total_elapsed_time DESC" -W

echo ""
echo "=== TOP 20 SLOWEST QUERIES (last ${HOURS}h) ==="
$SQLCMD -Q "
SELECT TOP 20
    distributed_statement_id,
    login_name,
    COALESCE(label,'') AS label,
    total_elapsed_time_ms,
    data_scanned_remote_storage_mb,
    LEFT(command, 120) AS command_preview
FROM queryinsights.exec_requests_history
WHERE start_time >= DATEADD(HOUR, -${HOURS}, GETUTCDATE())
ORDER BY total_elapsed_time_ms DESC" -W

echo ""
echo "=== TOP 10 MOST FREQUENT ==="
$SQLCMD -Q "
SELECT TOP 10
    query_hash,
    execution_count,
    median_total_elapsed_time_ms,
    LEFT(last_query_text, 120) AS query_preview
FROM queryinsights.frequently_run_queries
ORDER BY execution_count DESC" -W

PowerShell Templates

Query to CSV

#Requires -Version 5.1
param(
    [Parameter(Mandatory)][string]$Server,
    [Parameter(Mandatory)][string]$Database,
    [string]$OutputFile = "results.csv"
)

if (-not (Get-Command sqlcmd -ErrorAction SilentlyContinue)) {
    Write-Error "sqlcmd (Go) not found. Install: winget install sqlcmd"; exit 1
}
$null = az account show 2>$null
if ($LASTEXITCODE -ne 0) { Write-Error "Not logged in. Run: az login"; exit 1 }

$query = @"
SET NOCOUNT ON;
SELECT ProductID, ProductName, Category, Price
FROM dbo.DimProduct
ORDER BY ProductName;
"@

sqlcmd -S $Server -d $Database -G -Q $query -W -s"," -w 4000 -h 1 -o $OutputFile
Write-Host "Written to $OutputFile"

Schema Discovery

#Requires -Version 5.1
param(
    [Parameter(Mandatory)][string]$Server,
    [Parameter(Mandatory)][string]$Database
)

if (-not (Get-Command sqlcmd -ErrorAction SilentlyContinue)) {
    Write-Error "sqlcmd not found. Install: winget install sqlcmd"; exit 1
}

Write-Host "`n=== TABLES ===" -ForegroundColor Cyan
sqlcmd -S $Server -d $Database -G -Q "SELECT table_schema, table_name, table_type FROM information_schema.tables ORDER BY table_schema, table_name" -W

Write-Host "`n=== ROW COUNTS ===" -ForegroundColor Cyan
sqlcmd -S $Server -d $Database -G -Q "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS rows FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY rows DESC" -W