sp_wcProject is a stored procedure designed to allow you to easily define the projection of columns used in a table, view, or table valued function and then optionally run the query. The column names may be specified using wildcards, hence the "wc" name prefix.  You can also specify to include all columns except the ones enumerated.

This procedure is particularly useful when making ad hoc queries against system objects, such as
sys.databases, which return a large number of columns, many of which are related via common substrings such as "ansi" or "is_", etc. You can also use it to display and/or capture the generated SQL query.  The displayed width of string variables, viz., CHAR, VARCHAR, etc. may be limited to a maximum size and the string data may be either right or left justified within the maximum specified size.

Run the proc with no arguments to view the full documentation and see the additional bells and whistles it provides as well as some examples. Also provided below is a set of sample queries using the proc to show how it can be used.

set nocount on
use master

declare @sql VARCHAR(8000)
set @sql=''
exec sp_wcProject 'sys.databases','name,database_id,reco%,%ansi%', @sql out
print @sql
exec (@sql)

exec sp_wcProject 'master.sys.database_principals',@maxWidth=18
exec sp_wcProject 'master.sys.database_principals','name,type%,%[_]id,%role%','P',@where='type=''S'''        -- 'P' means print the SELECT statement instead of running it

exec sp_wcProject 'sys.databases','name,database_id,%desc,%ansi%',@where='owner_sid<>1',@orderBy='name',@maxWidth=10
exec sp_wcProject 'sys.databases','name,database_id,%desc,%ansi%,-log%,-page%',@where='owner_sid<>1',@orderBy='name',@maxWidth=10
exec sp_wcProject 'sys.databases','-source%,-co%,-is%,-%sid,-%guid,-log%',@where='owner_sid<>1',@orderBy='name',@maxWidth=10
exec sp_wcProject 'sys.databases','name,is%,-%on'

exec sp_wcProject 'dbo.authors','%name%'  -- This will fails unless there is an "authors" object in the master db
exec sp_wcProject 'pubs.dbo.authors','au%',@orderby='au_lname,au_fname'
exec sp_wcProject 'northwind.sys.sysfiles','%name%,%id','p'        -- 'p' means print the SELECT statement instead of running it

exec sp_wcProject 'sys.master_files','%name%,%desc,is%',@orderbyname=0
exec sp_wcProject 'sys.master_files','%name%,%desc,is%',@orderbyname=0,@maxwidth=20
exec sp_wcProject 'sys.master_files','%name%,%desc,is%',@orderbyname=1,@maxwidth=20
exec sp_wcProject 'sys.master_files','%name%,%desc,is%,-%only',@orderbyname=1,@maxwidth=20

exec sp_wcProject 'pubs.sys.fn_dblog(null,null)',
                  'current_lsn,operation,page_id,slot_id%row%,Checkpoint%,M%LSN,spid,Tran%Begin%,lock%,%contents%'
exec sp_wcProject 'pubs.sys.fn_dblog(null,null)',
                  'current_lsn,operation,page_id,slot_id%row%,Checkpoint%,M%LSN,spid,Tran%Begin%,lock%,%contents%',
                  @maxWidth='-22B'    -- To add a trailing "B" to convert binary to char you must pass a string value
exec sp_wcProject 'pubs.sys.fn_dblog(null,null)',
                  'current_lsn,operation,page_id,slot_id%row%,Checkpoint%,M%LSN,spid,Tran%Begin%,lock%,%contents%',
                  @maxWidth=-15

exec sp_wcProject 'pubs.dbo.discounts'
exec sp_wcProject 'pubs.dbo.discounts','-%qty',@maxWidth=10

exec sp_wcProject 'pubs.dbo.authors'
exec sp_wcProject 'pubs.dbo.authors',@maxWidth=5
exec sp_wcProject 'pubs.dbo.authors',@maxWidth=-5

DECLARE

@IndexId INT,
@IndexSize BIGINT,
@TableName NVARCHAR(256),
@IndexName VARCHAR(256) @TableName = ''
SET @IndexName = ''
SET @IndexSize = 0

SELECT

SET

-- Replace with actual table name and with index name