PRB: Some Arguments in sp_sproc_columns and sp_columns Support Wildcard Pattern Matching (259372)

SYMPTOMS

The SQL Server Books Online description for the sp_sproc_columns stored procedure incorrectly states that pattern matching is not supported for the @procedure_name and @procedure_owner parameters.

The following quote is from SQL Server Books Online:

[@procedure_name =] 'name'
Is the name of the procedure used to return catalog information. name is nvarchar(390), with a default of %, which means all tables in the current database. Wildcard pattern matching is not supported.

[@procedure_owner =] 'owner'
Is the name of the owner of the procedure. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is not supported.

However, wildcard pattern matching is supported for both arguments.

SQL Server Books Online also states that pattern matching is not supported in the sp_columns (T-SQL) topic for the @table_name and @table_owner arguments. This is also incorrect, pattern matching is supported.

WORKAROUND

If you want to locate a procedure that contains wildcard symbols in its name, enclose the wildcard symbols in square brackets as described in the "Pattern Matching in Search Conditions" topic of SQL Server Books Online.

For example, suppose that the user-defined stored procedures 'test_a' and 'test2a' have been created as follows:

USE pubs
CREATE PROCEDURE test_a AS
SELECT * FROM authors
GO
CREATE PROCEDURE test2a AS
SELECT * FROM titles
GO

When you run:

sp_sproc_columns 'test_a': Information is retrieved for both procedures, due to the presence of the wildcard underscore character.
sp_sproc_columns 'test[_]a': Only retrieves information for 'test_a'.