snippet

Easily view the cumulative I/O stats for each SQL Server database file.

A straight forward approach to enabling of disabling the automatic replication of schema changes in transactional replication.

A script to get you on the easy road to page-split baselining.

Easily decrease the fill factors on all of those uniqueidentifier/GUID columns.

A quick and efficient view to find duplicate statistics on your SQL Server tables.

A quick and efficient view to find duplicate indexes on your SQL Server tables.

A script to quickly retrieve the count of rows from all tables in a database.

Allows you to easily select from a file on the file system as if it were a table.

SplitArray is a powerful table SQL function which splits delimited values into a row-set.

Returns a table with a given number of rows.

Returns a table with one row per day of a given year and month.

Two simple functions to get the first and last day of a month.

Easy currency formatting of numbers in SQL Server using a scalar function.

Easy formatting of numbers in SQL Server using a scalar function.

SQL functions used to left and right pad a values with 0s (or some other value),

Ever stepped back and looked at the enourmous barrage of unused SQL Agent Schedules? Clean them up!

Need to format a name for a form? Well, this isn't perfect by any means, but it's a start.

Microsoft's ListView... What is it good for?! Well, I've got a few ideas...

A collection of queries covering the IO DMVs: per-file IO stats, pending IOs, IO by drive letter and index space usage.

A short script showing how to rename a SQL Server instance's registered name and verify it.

A script that compares Page Life Expectancy against a computed target and reports buffer pool health as a percentage.

A table valued function that splits text into words on spaces and punctuation, remembering each word's position.

A view that splits the current user name into Domain and Username columns.

A collection of queries covering the current-activity DMVs: sessions, connections, requests, waiting tasks and blocked processes.

A script generator that produces the T-SQL needed to change the agent credentials for log reader, snapshot and distribution agents across a publication.

A script that generates create or drop statements for every primary key in the database, preserving clustering and column direction.

A script that scans actual decimal values and recommends the tightest fitting data type for each column, with ALTER scripts.

A one-line query that returns the SQL Server instance's last restart time.

A query that lists every database principal lacking the CONNECT permission and generates a grant script for each.

A view that parses stored procedure names out of RPC:Completed rows in a trace table.

A table valued function that returns one row per day between a start and end date.

A query that reports the last seek, scan or lookup date for every user table using index usage statistics.

A query that lists all SQL Agent jobs currently executing with their elapsed time.

A query that measures CPU consumption per SPID over a 30-second window with full SQL text.

A script that scans actual string lengths and recommends right-sized varchar/nvarchar columns with ALTER scripts.

A one-shot server trace that runs for a fixed duration, captures the standard tuning events, reads the results back and deletes the file.

A stored procedure that enables or disables all foreign key constraints on a table, optionally including referencing tables.

A query that reports snapshot isolation version store disk usage per database relative to tempdb.

How to repair a single orphaned database user with sp_change_users_login Auto_Fix.

A scalar function that counts how many Windows-style carriage return / line feed (CRLF) pairs exist inside a string.

A collection of queries covering the indexing DMVs: operational stats, usage stats, fragmentation and missing index recommendations.

A last-resort script for removing a publisher and its distribution database from the distributor when normal unconfiguration fails.

A Resource Governor starter setup with workload groups and a classifier function routing by login and application.

The original SQL Server 2000 version of sp_help_revlogin using sysxlogins and sp_addlogin.

A collection of queries covering the CPU-related DMVs: schedulers, signal vs resource waits and the processes currently using CPU.

A script that reports free space in gigabytes for every file in every online database on the server.

The complete grant script for giving a user read-only SQL Server Agent visibility.

A script that test-compiles every procedure, function, trigger and view in the database and reports the ones that fail.

A query that reports how long each of a specific application's queries has been executing.

The SQL Agent job definition that runs SendErrorAlerts every ten minutes to surface replication errors.

A trick for forcing the distribution agent through a snapshot cycle without actually delivering any data.

A cursor-driven script that audits every permission in every database and shows which roles inherit them.

The DBCC SHOW_STATISTICS variants for inspecting index statistics headers, density vectors and histograms.

A query that lists the top ten queries by total CPU time with per-execution averages.

A stored procedure that finds every table and column containing a given GUID across the whole database.

Two statements that disable or enable all foreign key constraints on a table at once.

A stored procedure that reports the host, domain and user name of the current session.

A script that sets a consistent file growth rate on every data and log file in every online database.

The two ALTER statements that turn on change tracking for a database and for an individual table.

A brute-force and dictionary SQL login password cracker that reports blank, username-as-password and cracked accounts.

A reference table of every SQL Server trace filter column id with its name and description.

A stored procedure that reports weighted notable query statistics from the Maintenance Data Warehouse over a time range.

A stored procedure that replaces one GUID with another across every uniqueidentifier column in a database.

A query that decodes SQL Agent job history durations into real datetimes and seconds for sorting and analysis.

A script that samples virtual file I/O statistics over ten minutes and reports per-file throughput and latency.

A script that polls a single SPID every second and breaks down its wait types, CPU and physical I/O until it sleeps.

The classic stored procedure that generates CREATE LOGIN scripts for every login on a SQL Server instance.

A query that reports missing index candidates with improvement measures and generated CREATE INDEX statements.

Two views that expose every statistic's columns and flag duplicate statistics on the same column set.

Queries that list every table and index partition with its data compression setting.

Two sp_changepublication calls that turn DDL (schema change) replication on or off for a publication.

A CTE query that lists every effective permission in the current database, tracing role grants back to their members.

A diagnostic query that finds replicated articles whose row filter procedures have gone missing from the publisher database.

A query that lists every session with its current transaction isolation level.

A query that reports full detail on every request currently executing, including the exact statement text.

A script that gives a user their own schema with full rights inside it while denying them the dbo schema.

A family of four scalar functions that return the greatest of two, three, four, or five values.

A script that forces a full-scan statistics update on every table regardless of the row modification counter.

How to use sp_browsereplcmds on the distribution database to inspect pending replication commands when diagnosing agent errors.

A per-database query that counts page splits per index from the transaction log.

A stored procedure that refreshes every view in the database and reports only the real failures.

A batch of sp_changepublication calls that reset a publication to native sync with anonymous and immediate sync disabled.

How to read a .trc trace file back into a table, with and without event class names.

The complete DDL for a ReplicationMonitor database that records replication throughput and errors over time.

A stored procedure that runs sp_spaceused against every table in a given database and returns one combined report.

Queries that report cumulative virtual file I/O statistics per file and per database.

A scalar function that extracts the inner text of a named tag from a simple XML string using substring math.

A stored procedure that wraps any single-column query into one delimited, optionally padded, comma-separated string.

A cursor-driven script that finds orphaned users in every database on the server with drop scripts.

A stored procedure that reports every constraint, computed column, view and index attached to a given table column.

A script that resets fill factors on all indexes to a standard policy, with lower fill for GUID-based indexes.

A query that finds long-running queries that are waiting, blocked or holding open transactions.

A script that generates drop and create statements for every foreign key in the database, optionally limited to one table.

A stored procedure that drops all foreign key constraints attached to a given table's column.

Stored procedures that write a table or any query's results to a delimited text file from inside SQL Server.

A query that reports tempdb space usage in GB per process, with login and command details.

A query that lists all active processes on the server with their current SQL statement, waits and resource usage.

A stored procedure that drops a column's default constraint without dropping the column.

A script that finds which databases contain a given user by searching sysusers across the whole server.

A script that generates statements to disable page locks on every index in the database.

A stored procedure that checks or repairs a database, handling single-user mode, options and UPDATEUSAGE for you.

A query that counts how many rows of each event class a trace table contains.

A short script that enables or repairs Service Broker on the msdb database.

A query that compares the size and count of compiled plans versus plan stubs in the cache.

A stored procedure that reports index fragmentation across every database on the server with ready-to-run rebuild statements.

The statements to enable snapshot isolation on a database and monitor active snapshot transactions.

A query that reports the top waits making up 95% of total wait time, split into resource and signal time.

A script that generates RESTORE DATABASE ... MOVE statements for every online user database on the server.

A script that generates the statements to enable change data capture on every table in the current database.

A script that audits SQL login passwords against a wordlist using PWDCOMPARE to find weak or dictionary-based passwords.

A stored procedure that truncates and shrinks the log file in every online database on the server.

A stored procedure that finds and deletes every row containing a given GUID across all uniqueidentifier columns in a database.

A scalar function that zero-pads mixed numeric and text values so they sort in natural order.

A script generator that produces a batch of tablediff and sqlcmd commands to compare and repair every replicated table on a subscriber.

A query that lists all cached plans with their handles and text so you can evict a specific one with DBCC FREEPROCCACHE.

A step-by-step procedure for dropping a single article from a publication and re-adding it without forcing a full reinitialization.

A stored procedure that prints nvarchar(max) values in line-break-aware 4000-character chunks.

A set-based SQL login password cracker that matches a word list against SHA-1 hashes with MAXDOP control.

A query that lists every permission granted in the current database with the principal, class and object.

A script that reports the count, allocated space and used space in gigabytes across all databases on the server.

A server trace that captures only deadlock graph events for a fixed window.

A scalar function that formats a datetime into any of about twenty named formats, including a UNIX timestamp.

A seven-step checklist for renaming a subscriber database and updating every replication table that references it.

A scalar function that returns the number of business days (Monday to Friday) in the month of a given date.

A reference table of every SQL Server trace event class id and its name.

A script that generates ALTER DATABASE MODIFY FILE statements to move every database's files to new locations.

A scalar function that sorts the pieces of a delimited string and rejoins them with the same delimiter.

A collection of queries covering the execution DMVs: query stats, cached plans and plan attributes for finding and analyzing queries.

A query that lists every sysadmin login on the server with its type and dates.

A stored procedure that scrapes log reader agent history from every distributor database and records hourly command throughput for reporting.

A query that reports the fragmentation percentage and page count of every index in the current database.

A stored procedure that generates the complete drop script for a user across every database: objects, schemas, users and login.

A view over sysjobhistory that converts the packed run date/time/duration integers into readable start and end times.

A query that finds all queries whose cumulative CPU worker time exceeds one hour, with plan handles for eviction.

A three-line recovery script that resets the log reader and flushes replication when pending reads must be abandoned.

A collection of queries covering the overall health DMVs: wait statistics, performance counters, system info and memory state.

A script that generates IN-list check constraints on low-cardinality columns to support table partitioning.

How to clear all cached plans associated with a specific Resource Governor resource pool.

A script that generates DROP INDEX statements for every non-primary-key index in the database.

A query that converts change tracking commit versions from sys.dm_tran_commit_table into local time.

A query that reports row counts and data size in GB for every user table in the current database.

A script that adds or removes the NOT FOR REPLICATION attribute on every identity column in the current database.

The complete grant script for giving a user full SQL Server Agent administration rights.

A detailed runbook for isolating and skipping a single bad transaction that has halted the log reader agent.

A query that finds orphaned users in the current database and generates a drop script for each.

The single grant statement that lets a login view server performance counters and DMVs.

A scalar function that formats a decimal value with thousands separators and an exact number of decimal places.

A collection of queries covering the memory DMVs: buffer usage by database and by object, plus Buffer Manager performance counters.

Two queries that generate CREATE USER and sp_addrolemember scripts for every user and role membership in a database.

A stored procedure that suggests index column order based on measured distinct value counts.

A short script showing how to move the tempdb data and log files to a new location.

How to list every running server trace and stop, start or delete one by id.

A script that switches every online database on the server to the SIMPLE recovery model.

A query that lists every enabled SQL login whose password was last changed more than 90 days ago.

A query that lists the ten longest-running queries currently executing with their statement text.

A worked example of polling CHANGETABLE(CHANGES) with an anchor version to pull incremental changes.

A stored procedure that finds which SQL modules reference a given string in their definitions.

A script that maps every table and column in the database with types, keys, foreign keys and extended property comments.

A comprehensive report of all blocked and blocking processes with wait details and full SQL text.

A query that maps every index to its table, filegroup and physical file.

A scalar function that returns the Nth token from a delimited string using a recursive CTE.

A fully annotated server trace template capturing the five standard tuning events with a complete commented filter library.

A cursor-driven script that reassigns the owner of every user database on the server to sa.

A server-wide DDL trigger that maintains a ResourceUserRoles table tracking every database role membership.

The SQL Agent job definition that records undistributed replication commands every fifteen minutes.

A scalar function that returns the time portion of a datetime as a string.

A recursive CTE query that orders all user tables by their foreign key dependencies, from referenced to referencing.

A trigger template that saves and restores @@IDENTITY around trigger logic.

A stored procedure that drops a column together with its default constraint, if one exists.

How to force-regenerate the service master key to fix replication decryption errors after a restore.

The four-step TDE setup: master key, certificate, database encryption key and turning encryption on.

A self-contained script that simulates repeatable deadlocks on demand for testing recovery code.

A complete walkthrough of cell-level data encryption using a master key, certificate and AES-256 symmetric key.

A script that measures page splits per second over a configurable time window using performance counters.

A continuous sampler that records per-session CPU, I/O and wait deltas over a configurable time window.

A stored procedure that builds a formatted list of a table's primary key columns with configurable format tokens and delimiters.

A stored procedure that disables or rebuilds all non-primary-key indexes on a table, with an exclusion list.

The step-by-step sequence for attempting to repair a database that has gone into the suspect state.

The SQL Agent job definition that runs the log reader and distribution command recording procedures every minute.

A cursor script that regenerates and executes ALTER USER WITH LOGIN statements for every mapped user in a database.

A script that populates a DimTime dimension table with every second of a day for SSAS time intelligence.

A stored procedure that defrags or rebuilds fragmented indexes and reports before/after scan density with timing.

A script that generates the complete CREATE DATABASE ... AS SNAPSHOT OF statement for the current database, including all data files.

A complete T-SQL implementation of the Metaphone phonetic algorithm for fuzzy name matching.

A query that reports the number of cached pages and buffer pool usage in GB for each database.

A script that estimates data compression savings for every table and generates rebuild scripts for the worthwhile ones.