Which databases on this instance hold the most disk, and how large is each one in MB and GB? One query against sys.master_files and sys.databases answers that for every user database, sorted with the largest first. Purpose and Overview Capacity planning starts with a number per database: how much file space each one …

Find Unused Nonclustered Indexes in SQL Server This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table. Purpose and Overview Every …

SQL Server Query to Identify Tables with Clustered Indexes for Rebuilding Database administrators frequently need to perform maintenance operations on clustered indexes to optimize database performance. This SQL Server query provides a quick and efficient way to identify all user tables that contain clustered indexes, …

Giving an application role read, write and execute rights on a database usually means one GRANT per table per permission plus one per stored procedure, which runs to hundreds of statements on a mid-sized schema. The script below writes those statements for you from sys.objects, schema-qualified and bracket-quoted, so …

Checking how much space every table in a database uses means running sp_spaceused once per table, and typing those calls by hand stops being practical after a dozen tables. This one-line query reads the object catalog and writes the sp_spaceused call for every user table, so the whole set can be pasted into a query …

SQL Server Dynamic Update Statistics and Select Scripts for Database Maintenance Purpose These SQL Server scripts are designed to automatically generate database maintenance commands by dynamically creating UPDATE STATISTICS statements and SELECT queries for all user tables in a database. This approach is particularly …

SQL Server DBCC DBREINDEX Script with Timing - Database Maintenance Guide Database maintenance is a critical aspect of SQL Server administration, and index rebuilding plays a vital role in maintaining optimal database performance. This comprehensive guide explores powerful DBCC DBREINDEX scripts that include timing …

SQL Server DBCC DBREINDEX Script Generator for Database Maintenance Database maintenance is crucial for optimal SQL Server performance, and one of the most important tasks is maintaining database indexes. The DBCC DBREINDEX command is a powerful tool for rebuilding indexes, but manually running it on every table can be …

DBCC CHECKIDENT with NORESEED reports the current identity value of a table next to the largest value actually stored in the identity column, without changing either. It works on one table per call, so checking a whole database means writing one command per table. The script below generates those commands from the …

SQL Server Database Table Row Count Report Script - Complete Guide Database administrators frequently need to monitor table sizes and row counts across their SQL Server databases. This comprehensive guide explores a powerful T-SQL script that generates detailed table row count reports using cursor-based iteration and …