Dec 01

Backup Restore Progress

I cannot take credit for this script, but I use it all the time:

 

Source

Feb 10

Cursor Template

Most of the time when I use a cursor I find myself using the same template. I am always writing the same code so I figured I’d save myself 3 minutes each time I use a cursor and store the code here:

 

Jun 13

Dynamic Log Shrink (DBCC Shrinkfile)

Recently I worked with a customer that was running their databases in simple mode. During the night they would run various bulk load jobs. This would cause the transaction logs to grow quite large (even though the database is set to simple mode each transaction must be logged). This in turn caused disk space issues.

A simple solution I created was to shrink the transaction logs on a daily basis using this dynamic script:


 

Jun 11

Foreign Key Relations

One of my colleagues wanted a textual way of viewing foreign key relations. It prompted me to write this script:

 

Jun 10

List Stored Procedures for All Databases

I recently needed a way to check all databases on an instance for a Stored Procedure that was created in the last 3 days. I created this script using the sp_MSforeachdb “undocumented” stored procedure to query each database:

 

Jun 05

Script Database User Role with Permissions

If you have ever tried to copy database security roles that included permissions or securables you might have noticed that SQL’s built-in script generator doesn’t produce a script that includes role permissions.
Here is a script that I found and modified it to work with legacy SQL versions:

Apr 12

Checking for sysadmin on an Instance

Here is a simple script that checks for sysadmins on an instance:

 

Apr 04

Sort or Search in sp_who2

Here is a simple script that allows you to sort and search for items in the sp_who2 stored procedure. Works with SQL 2005 and up:

 

Feb 04

For Each Database (EXEC sp_msForEachDB)

Here are some useful examples of the use of the undocumented sp_msForEachDB command:

Basic:

Quotes are complicated :

Note the number of single quotes needed to print is 4 (normally 2).