Practical database scripts, cleaned for public reuse.
Start with the intent. Read the safety notes. Adapt the placeholders. Then run.
SQL Field Notes is a curated collection of database diagnostics, maintenance helpers, data-shaping patterns, and administration recipes. It grew out of real project work, but every public sample is deliberately separated from customer schemas, company data, credentials, server names, and environment-specific database names.
This repository was formerly presented as Playing With SQL Query. The new name better reflects what it has become: a field guide of small, useful patterns that are easy to understand and safer to adapt.
A useful query is more than SQL that happens to run. It should explain what it observes, what it can change, and what you must customize first.
| Concern | Repository convention |
|---|---|
| Can I run it safely? | Every featured script declares whether it is read-only, generates commands, or changes state. |
| What must I change? | Environment-specific values use obvious placeholders or neutral sample objects. |
| Will it leak project details? | Contributions must remove customer data, internal names, hosts, credentials, and connection strings. |
| Which engine is it for? | Scripts are grouped by database engine and call out version requirements when relevant. |
| Can I trust an automated recommendation? | Diagnostic output is evidence to review, never permission to apply a change blindly. |
The newest field-tested additions are read-only SQL Server diagnostics distilled from patterns found across the local project archive and rewritten for public use:
| Script | What it answers | Safety |
|---|---|---|
| DatabaseHealthCheck.sql | Which tables are largest, which indexes are quiet, which foreign keys are untrusted, and where fragmentation is high? | Read-only |
| ActiveRequestsAndBlocking.sql | What is running now, who is blocking whom, and which statement is active? | Read-only; server-state permission |
| IndexUsageAndCandidates.sql | Which nonclustered indexes cost more writes than the reads they serve? | Read-only; review over a full workload cycle |
| ForeignKeysWithoutSupportingIndexes.sql | Which foreign keys do not have an index with the same leading columns? | Read-only; SQL Server 2017+ |
| IndexFragmentationReport.sql | Which sufficiently large indexes merit reorganize or rebuild review? | Read-only |
Two least-privilege templates round out the cross-engine collection:
- MySQL/CreateReadOnlyUser.sql creates a database-scoped reporting account without global privileges.
- Oracle/CreateApplicationUser.sql creates a schema owner with an explicit, minimal privilege set.
| Engine | Typical topics |
|---|---|
| SQL Server | live diagnostics, indexes, metadata, partitions, data cleanup, geospatial helpers, and logon controls |
| PostgreSQL | metadata discovery, sequence-gap analysis, dblink, and role grants |
| MySQL | least-privilege users, missing-index generation, and table/index maintenance |
| Oracle | schema users, session metadata, table documentation, and column discovery |
| Db2 | connection, catalog, backup, restore, and administration notes |
See the full catalog for a task-oriented index.
- Open the script and read its Purpose, Safety, Requirements, and Customization header.
- Replace values such as
<DATABASE_NAME>,<SCHEMA_NAME>, or neutral sample objects. - Run read-only diagnostics against a test or restored environment first.
- Inspect generated commands before executing any of them.
- Measure before and after; DMV counters, cached plans, and fragmentation are observations, not universal prescriptions.
- Read-only — queries metadata or runtime state and does not intentionally mutate database objects or data.
- Generates commands — prints or returns DDL/DML for review; output is not executed automatically.
- Changes state — creates, alters, updates, deletes, kills sessions, or changes server/database configuration.
Some read-only diagnostics can still be expensive on very large or busy systems. Read each header and narrow its filters when needed.
Never commit:
- passwords, tokens, API keys, or connection strings;
- public or private IP addresses tied to a real environment;
- customer, company, product, server, host, or internal database names;
- production row samples, personal data, or business-specific identifiers;
- destructive commands that execute automatically without a visible safety gate.
Use neutral names such as SampleDatabase, app, Orders, and ReportingUser. Keep secrets outside the script and inject them through the database client or your secret manager.
The contribution checklist, header template, and naming rules live in CONTRIBUTING.md. Small, focused scripts are preferred over database dumps or application migrations.
See CHANGELOG.md for the re-brand and safety-focused changes.
MIT © PeopleWorks Software Team. See LICENSE.
Created by Pedro Hernández — PeopleWorks, Microsoft MVP for .NET
PeopleWorks database tools — DBFSync moves legacy DBF data · SQLDiff moves and validates the schema · SyncJob moves relational data · SQL Field Notes shares the queries learned along the way
📖 DBFSync guide · 📖 SQLDiff guide · 📖 SyncJob guide
Every field note here came from solving real database work, not from a whiteboard.
MIT licensed — use it, fork it, ship it.
© 2026 PeopleWorks