This tip continues the system stored procedure series with two simple routines.

The SQL code in Listing 1 creates a system stored procedure named sp_FindColumn.  The routine returns a list of table/column names that match a specified combination of search criteria.

The SQL code in Listing 2 creates a system stored procedure named sp_AnalyzeColumn.  The routine returns a frequency distribution (counts and percentages) of the values in a specified table/column.

These two routines introduce several SQL coding habits that will be common among many of the stored procedures in this series.  The method of selecting objects (usually tables) to work with will be seen frequently and it's described below.  Many of the routines contain a local variable, @TPre, that can be used to specify a table name prefix.  If a table name prefix is identified with this variable then the prefix can be omitted from table names provided as parameters.  The many stored procedures were designed to be a package and there was a very heavy emphasis on consistency among the routines.  A temporary table is used in the sp_FindColumn routine, even though not necessary, because it provides structural consistency with several other similar routines.  The consistency should be beneficial for understanding and/or modifying the routines.  However, the naming of things within the SQL code is not nearly as beneficial.  The parameters, the local variables, the temporary tables, and some of the columns in result sets are named rather capriciously.  I humbly beg your forgiveness for my programming idiosyncrasies!  The stored procedures do some really wonderful things (as you will see in future tips), but they definitely reflect my weird style.

The sp_FindColumn stored procedure accepts five parameters.  All of them are optional, but typically one or two of the first four are used.

The first four parameters work together to form a combination of search criteria using object names.  In most of the future routines in this series the parameters are used to select tables, but in this routine they are used to select columns.

The first and second parameters are lists of column names separated by pipes (vertical bars).  The first parameter specifies column names to be included.  The second parameter specifies column names to be excluded.

The third and fourth parameters offer column selection based on pattern matching of names.  The third parameter includes columns by using a LIKE operator on the names.  The fourth parameter excludes columns by using a NOT LIKE operator.

The effects of the first four parameters are combined with AND operators.  Often only one of the parameters would be used for a given call, but it may be useful to provide the second and/or the fourth in combination with the third in order to work with the desired subset of objects.  If the parameters are omitted, or null values are provided, they are effectively ignored for selection purposes.

The fifth parameter affects the output result set.  A value of zero (0) removes the prefix from table names (if a prefix is identified) and a value of one (1) does not.

This routine is handy for finding columns by name and listing the table(s) in which they exist.  The listing includes table name, column name, ordinal position, data type, data size, width/precision, and scale.

The method used to select objects in this routine is not necessarily the most efficient way of handling delimited strings.  The CHARINDEX function was used for simplicity because performance is not a significant issue when referencing tables with very modest row counts in an administrative routine.  If this kind of selection task were being done in a production routine that referenced tables with larger row counts it would probably be better to parse the delimited string and use the result set for a join (please refer to my first tip on this site for some relevant SQL code).

This example lists the columns that start with the word Ship in the Northwind database:

USE Northwind EXECUTE sp_FindColumn NULL,NULL,'Ship%',NULL,1

The sp_AnalyzeColumn stored procedure accepts two parameters and both of them are required.

The first parameter can be a table name, view name, table-valued user-defined function reference, or a SELECT statement.

The second parameter is a column name within the result set described by the first parameter.

This routine is handy for checking the selectivity of a column to help determine if an index would be beneficial.  The result set includes data value, row count, and percentage.

The sp_AnalyzeColumn stored procedure uses dynamic SQL code.  In fact, several of the stored procedures in this series use dynamic SQL code to provide very powerful functionality.  There are ramifications (such as security issues) when using dynamic SQL code within production routines, but it should not be a major problem within administrative routines.

This example analyzes the Country column in the Customers table of the Northwind database:

USE Northwind EXECUTE sp_AnalyzeColumn 'Customers','Country'

I hope you find these two system stored procedures to be useful.