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.
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.
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.
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.
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.
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)
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.
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.
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.
Generate recommended indexes using system DMVs. Consolidate the recommended indexes and consolidate the recommended indexes with the existing indexes. See the full description on our page SQL Server Index Automation.