Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

sp_wcProject

By Anthony Zackin,

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.

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.

How It Works
===========

The second argument, @colnames, is an optional comma-separated list of terms with optional wildcards which
are used to choose the columns to display from the first argument object. The proc creates a unique global
temporary table, ##wcProjectNNNNN, for each connection (NNNNN = @@SPID) which will get loaded with a list
of the columns and their default order from the sys.all_columns table, e.g.,

SELECT name, column_id INTO ##wcProject42 FROM sys.all_columns WHERE object_id = OBJECT_ID(@object)

If @object is 'PUBS.TITLEAUTHOR' then the resulting table will be

name column_id
------------------------------
au_id 1
title_id 2
au_ord 3
royaltyper 4

Each term in the comma-separated list, @colnames, is used to filter this table to determine which
columns should be included in the select list which is built using a dynamic SQL statement. For
example, if @colnames = 'au%,royalty%' then code similar to the following is generated to create
the select list "[au_id],[au_ord],[royaltyper],":

DECLARE @sql VARCHAR(8000)
SET @sql = ''
SELECT @sql = @sql + '[' + name + '],' FROM ##wcProject42
WHERE name LIKE 'au%' OR name LIKE 'royalty%'

The select list is cleaned up and the optional WHERE and ORDER BY clauses are concatenated to it,
when necessary, to create the final SQL statement which is either executed or returned to the caller
as requested. The selected columns may be ordered alphabetically or via their default ordering hence
the need for the "column_id" column.

-- Examples --
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%'
 ,@top=8
-- Show row log contents but limit its width also by converting it to character:
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
 ,@top=8
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,@top=8
exec sp_wcProject 'pubs.dbo.discounts'
exec sp_wcProject 'pubs.dbo.discounts','discount%',@maxWidth=12
exec sp_wcProject 'pubs.dbo.authors'
exec sp_wcProject 'pubs.dbo.authors',@maxWidth=5
exec sp_wcProject 'pubs.dbo.authors',@maxWidth=-5

Total article views: 827 | Views in the last 30 days: 3
 
Related Articles
FORUM

Select premission was denied on Object

Select premission Denied on Object

FORUM

Not Selecting All Columns in select query

Not Selecting All Columns in select query

FORUM

Gap sequence of column_id

select * from sys.columns where object_id=object_id('table') returns gap in sequence of column_id

FORUM

sp_executesql dynamic columns in select statement

sp_executesql dynamic columns in select statement

FORUM

column into a row using select

column into a row using select

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones