SQL Server Magazine

T-SQL Stored Procedure Creates Trace Tables from Trace Files

LoadTraceFileToTable loads the data from a trace file into a trace table so that you can easily sort and analyze that data.

T-SQL UDF Quickly Determines How Often a Substring Appears

To determine the number of occurrences of a substring within a string, a user-defined function (UDF) performs a simple arithmetic calculation.

Protect Your T-SQL Objects from Deletion

The sp_markAllMasterDBasSystem stored procedure can help protect the T-SQL objects you've created by marking them as system objects.

Procedure Helps Pinpoint Problematic Processes

Here's a stored procedure that displays all the T-SQL statements currently being executed along with the IDs of the processes running those statements.

Easily Find Rows That Violate Constraints

The showViolatingRows procedure automates the tedious task of finding rows that violate check and foreign-key constraints.

Summarize All the Constraints for a Table

The sp_showConstraints stored procedure is an alternative to using sp_helpconstraint when you need to find all the constraints for a table.

Optimize the Size of Your Indexed Integer Columns

Together, the suggestIntType and scanIntIndex procedures search for indexed integer columns in a database and determine whether the optimal data type is used for those columns.

Rename Files or Directories On the Fly in T-SQL Scripts

Stored Procedures Work in Concert to Import and Export Data

Here’s a simple yet generic solution for exporting or importing data. This solution consists of two stored procedures: BCPObject and ExpOrImpDB.

Quickly Retrieve a List of Available Media Resources

The sp_GetAllSQLMediaTypes stored procedure lists the names of all the available media and their types on a SQL Server machine.

Extend the sp_spaceUsed Procedure’s Usefulness in Obtaining Transaction Log Statistics

By compiling sp_LogSpace in a master database and calling it from an updated version of sp_SpaceUsed, you can extend the ability of sp_SpaceUsed to obtain transaction log statistics.

Don’t Let Resource-Intensive Sessions Consume All Your Resources and Time

The getMaxResourceConsumeSessions stored procedure lets you quickly identify the sessions that are consuming most of a SQL Server machine's resources.

Dynamically Obtain a SQL Server’s IP Address in T-SQL Code

The getSQL_IPaddr.sql procedure uses the Ipconfig utility to dynamically find the IP address of a server running Microsoft SQL Server.