This is our collection of SQL Server scripts that we find useful.

We hope you find them useful too.

Administration Scripts

SQL Script Description	Script Link
Backup all databases on a server. Creates the script for later use or performs the backups. This is useful on servers that are not part of your normal backup plan (i.e., development server) or for backups outside of your normal schedule.	ADMIN.Backup.Script.Create.sql
Create BCP statements for all tables specified (all in the database by default). Creates the IN / OUT or both.	ADMIN.BCP.Create.In.Out.sql
Runs compression analysis on all tables in a database. Shows the estimated results of PAGE and ROW compression for each table in the database.	ADMIN.Compression.Estimates.sql
Change the owner for all databases on a server to the same owner.	ADMIN.Database.Owners.Change.sql
Drop specified users from all databases. Option to drop schemas.	ADMIN.Drop.Users.sql
List all SQL Jobs. Steps are included as an XML column. Schedules are included as an XML column.	ADMIN.Msdb.Jobs.List.sql
Shows which columns might benefit from being defined as sparse columns. Uses the MSDN supplied thresholds. For data types with multiple definitions the more conservative value is used for all types.	ADMIN.Sparse.Column.Calculations.sql
Create the SQLCMD scripts to run all of the scripts in a directory. The scripts can be generated or executed by the command.	ADMIN.SQLCMD.Execute.All.Scripts.sql

 

Process Scripts

SQL Script Description	Script Link
Shows current runnable statements on SQL Server. Also shows the query plan when available and the query text.	PROCESSES.Current.Runnable.Statements
Stops (KILL)s all the processes on the specified database.	PROCESSES.KILL.All.For.Database

Aggregate Analysis Scripts

SQL Script Description	Script Link
Generate a SQL Statement that finds the mininum, maximum, average or standard deviation length of every numeric column in a table.	AGGREGATE.Aggregate.Single.sql
Run the main aggregate functions for each numeric column in a table.	AGGREGATE.Aggregates.UNPIVOT.sql
Generate a SQL Statement that finds the average length of every column in a table.	AGGREGATE.Avg.Length.sql
Check all the CODE / FLAG values in the specified table. This is useful with poorly designed tables (flat tables) or on development / staging tables or when researching new data. If the database is fully normalized there usually won’t be multiple code values in a single table.	AGGREGATE.Code.Flag.Analysis.sql
Scan a table and count the number of NULL values in each column. Same as AGGREGATE.NULL.Analysis in stored procedure format. This is a prerequisite for other scripts.	AGGREGATE.dbo.prcNaGenerateNullAnalysisAdHoc.select.sql
Generates / runs a SQL Statement that finds the maximum length of every column in a table.	AGGREGATE.MAX.Length.sql
Scan a table and count the number of NULL values in each column.	AGGREGATE.NULL.Analysis.sql

 

Research Scripts

SQL Script Description	Script Link
Scan a database server and count simple object types in each database.	COUNT.AllDatabase.Objects.Simple
Scan a database server and count all object types in each database.	COUNT.AllDatabase.Objects
Provide a frequency count for the characters in a column.	COUNT.Characters
Does a row COUNT on all tables in the current database.	COUNT.Rows.All.Tables
Provide a frequency count for the words in a column.	COUNT.Words
Return the top X rows for each table in a database. Generates the commands that will create the output. Run the script in TEXT mode. Run the output in the command line to generate the rows.	GENERATE.AllTables.SQLCMD.TopXRows
List all data types and their associated base types.	LIST.AllDataTypes.With.Base.Types
Return all filegroups and their size for all databases.	LIST.AllFilegroups
Show the x most recently modified objects on the server.	LIST.AllObjects.CreatedBy.Date
Shows the columns used in each table in a database. Useful for showing common columns.	LIST.AllTables.ColumnsBy.Table
Show all tables for the current database.	LIST.AllTables
Show all tables for the current database. List all of the columns and creation details for all tables in the current database.	LIST.AllTables.With.Column.Type
Scan a database server looking for objects, columns or object text containing the specified search string. Searches all databases.Useful when gathering technical specifications or trying to find all instances where a text string is used.Will search for the following objects: Tables Columns Views Stored procedures Functions Agent jobs SSIS packages SSRS reports Logins, Users, Roles, Schemas Text within unencrypted stored procedures, functions, triggers Functions that do a SELECT FROM another source (no other searches are performed with this search)	SEARCH.AllDatabases.AllObjects.Text
Find all columns of the specified type in a server.	SEARCH.AllTables.AllDatabase.ColumnsOfType
Find a value in any column in any table in a database. Use caution running this on non-development or shared environments. It can be very resource intensive. It does a complete table scan on every table in the database.	SEARCH.AllTables.FindAnyValue
Shows all of the tables in a database. Shows the identity column for a row, if it exists, or the first column based on the PARTITION ORDER BY.	SEARCH.AllTables.IDENTITY.Status
Scan a database server recently run queries still in the cache. Useful when trying to find a query that was run but not saved or getting a quick look at what has been run on the server.	SEARCH.Recent.Queries
Scan a database server and give a very rough estimate for development time based on the number of stored procedures, functions, tables and views. Time is also based on number of columns, number of functions or complex procedures used and number of bytes of code.This was used to give a VERY rough estimate on how long it would take to re-create all of the databases and objects in a server when moving to a new vendor and platform. It will need to be modified heavily to apply to your environment.	SIZING.AllDatabase.Tables.StoredProcedures.Functions
Show the maximum possible row length for a table. Does not include columns with BLOB or XML data.	SIZING.AllTables.MaxRowLength
Create a size estimate (in MB) for a table based on the number of estimated columns of each type.	SIZING.TableSpace.Estimate

 

Index Scripts

SQL Script Description	Script Link
List all the clustered indexes on all tables in the current database.	INDEXES.Clustered.Indexes

 

All scripts and code Copyright (C) I-Tech Solutions, Inc. under Microsoft Reciprocal License (MS-RL) http://www.microsoft.com/en-us/openness/licenses.aspx unless otherwise noted.

It is your responsibility to ensure that the scripts work as expected in your environment. No warranty is expressed or implied.