Meta

Category: Sql

Very often when working in databases we have to resort to dynamic SQL generation to solve a problem. For example, common instances of this would be when performing an action across a number of different tables, or performing a PIVOT on a data set where the set of pivot results is not pre-determined. This latter example is one which I’ll take through this walk through.

The scenario is that we have a table of Person data and a table of Payslip data, and the requirement is to return each Person with the salaries listed out year on year. Its a simple example, but not unlike a real-world problem.

An easy way to improve the usability of sp_who2 is to make a database specific version.

The idea is to leverage the information provided by sp_who2, but provide a filter for a given database name. This is really handy on either production or development SQL servers with large numbers of databases.