The following T-SQL script will extract all data/log files for the currently attached databases, and then scan all of those directories to find files that are not being used by SQL Server. This will quickly locate mdf, ldf or ndf files located in your data and log directories to free up space.

Ever wonder when the last time your table stats where updated (perhaps done automatically because auto_stats is on?).

The following sproc will generate a random complex password, and return it as an output parameter from the caller. Complex means it will be guaranteed to contain one upper and one lower case letter, one number 0-9, and one special character.

Somewhat buried IMHO, the following command will enable network access for DTC, which is off by default. This required when installing SQL Server 2000/2005 and you want to allow for things like remote two-phase commit, linked servers and replication. Hardest part was finding the actual component name to place in the sysocmgr file.

The following will generate the SQL statements for the specified role or user. In the example below, it generates all GRANT or REVOKE statements for the public role:

SQL Server 2000 suffers from a major problem when it comes to collation. It's a one-way street for every textual column in your databases. The following steps have worked for me at converting a large databases from one collation to another without having to BCP or create a new database, move data around or change individual columns.

Quick and dirty, this sproc will generate the sp_attach_db T-SQL statement for a specified database...this coupled with sp_MsforEachDb '#script_attach ''?''' will generate the attach script for all databases on an instance

Using the below T-SQL, you can narrow the results of fragment tables within the current database. The data is obtained by running DBCC SHOWCONTIG on all tables, and the results is ordered by the the Number of Pages x Scan Density, descending.

Using the SQL function fn_virtualfilestats, you can retrieve cumulative IO information for each database since SQL server started. The following T-SQL script creates a multi-function sproc that when scheduled via SQL Agent job, will periodically save a copy of current fn_virtualfilestats and calculate the difference between the last stored file stats.

The following T-SQL script will generate a backup schedule report from transaction log and data backup plans as well as SQL Lightspeed jobs.