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 is described below. Many of the routines contain a local variable, @TPre, which 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 heavy emphasis on consistency among the routines.
A temporary table is used in the sp_FindColumn routine, even though it's 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, local variables, 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 r...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
outines 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 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 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.
Click for the stored procedures: sp_FindColumn and sp_AnalyzeColumn
Note from the author: These tips are not about stored procedures in a user database. They present generic stored procedures that should be useful to invoke from any user database. Such stored procedures could exist in each user database, but that results in maintenance issues. I have specifically presented the generic stored procedures to be created in the master database. When a stored procedure exists in the master database AND has the "sp_" prefix it offers a great feature that it otherwise could not provide. That is, it can be invoked from any user database and it will execute in the context of that database. The generic stored procedures in the master database MUST be prefixed with "sp_" in order to reference objects in the calling database without having to qualify those references with the database name.
About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has over 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Brian is a software developer, database developer, database administrator, and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
More information from SearchSQLServer.com
Tip: A simple stored procedure to list database objects by selected type(s)
Tip: User-defined functions to generate and apply a table of sequence numbers
Checklist: Retrieve filtered file lists from a specified directory
