sp_wcProject

,

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

USE MASTER

-- =============================================
-- Author:	Anthony Zackin
-- Create date: 2009-01-27
-- Description:	Right justifies text in a varchar
-- Arguments:	First argument is the string to justify.
--		Argument 2 is the length of the right justified text.
-- Result:	An VARCHAR(8000) with a length of @outLen containing
--		the text of @s right justified to @outLen characters.
-- Example:	SELECT CAST(DBO.fnJustRight('abcdefgjijklmnopqrstuvwxyz', 25) AS CHAR(25))
--		==> "bcdefgjijklmnopqrstuvwxyz"
-- =============================================
IF OBJECT_ID('master.dbo.fnJustRight') IS NOT NULL DROP FUNCTION dbo.fnJustRight 
GO

CREATE FUNCTION dbo.fnJustRight 
(
	@s VARCHAR(8000), @outLen int
)
RETURNS VARCHAR(8000)
AS
BEGIN
	RETURN SUBSTRING(@s, 1+LEN(@s)-@outLen, @outLen)
END
GO

IF OBJECT_ID('sp_wcProject') IS NOT NULL DROP PROC sp_wcProject
GO

CREATE PROC sp_wcProject @object sysname = NULL, 
			@colNames VARCHAR(8000) = '%', 
			@sqlString VARCHAR(8000) = NULL OUT, 
			@orderByName BIT = 0,
			@maxWidth VARCHAR(5) = NULL,
			@where VARCHAR(8000) = NULL,
			@orderBy VARCHAR(8000) = NULL,
			@top VARCHAR(10) = ''
AS
	SET NOCOUNT ON

	DECLARE @n int, @fn_args VARCHAR(1000)
	SET @fn_args = ''
	SET @n = CHARINDEX('(', @object)
	IF @n > 1	-- Don't allow "(" as the first character
	BEGIN
		SET @fn_args = SUBSTRING(@object,@n,1+LEN(@object)-@n)
		SET @object = SUBSTRING(@object,1,@n-1)
	END 

	IF OBJECT_ID(@object) IS NULL
	BEGIN
		IF @object IS NOT NULL AND @object NOT LIKE '%?%' 
		BEGIN
			PRINT 'The object, ''' + @object + ''', does not exist in the current database: ''' + db_name() + ''''
			PRINT 'Omit the @object argument for help.'
			RETURN
		END
		PRINT 'sp_wcProject version 3.0.0 [Copyright 2009 Anthony Zackin, Takamomto LLC]'
		PRINT ''
		PRINT 'Syntax:'
		PRINT ''
		PRINT 'sp_wcProject ''[ [ <database_name> . ] <schema_name> .] [@object=]<table, view, or table-valued function>'''
		PRINT '              [, [@colNames=]''<[-]column name pattern> [ , ...n ] ]'''
		PRINT '              [, [@sqlString]=<''P'' | a variable declared as VARCHAR(8000)>]'
		PRINT '              [, [@orderByName]=0|1]'
		PRINT '              [, [@where=]<where clause>]'
		PRINT '              [, [@orderBy=]<order by column list>'
		PRINT '              [, [@maxWidth=]<[-]maximum width of character columns[B]>'
		PRINT '              [, [@top=]<number which limts rows returned>'
		PRINT ''
		PRINT '- An invalid or missing @object will cause this help text to be displayed.  You should'
		PRINT '  fully qualify the @object argument to be safe, e.g., ''pubs.dbo.authors''.'
		PRINT ''
		PRINT '- This proc will display the contents of the required argument, @object, a table, view, or'
		PRINT '  table-valued function, displaying the columns which are specified in the comma-separated list'
		PRINT '  argument @colNames.  This argument list should contain full column names or partial names with'
		PRINT '  wildcards to match a set of columns. If @colNames is omitted then all the columns are included.'
		PRINT '  Note: you can use an underscore to match embedded spaces in a column name.'
		PRINT ''
		PRINT '- Column name token strings in @colNames may begin with a minus sign to indicate that all columns'
		PRINT '  which match the token should be EXCLUDED from the projection.  If only excluded values are'
		PRINT '  specified then all columns not excluded will be displayed.  For example, if @colNames=''-is%'''
		PRINT '  then all column names not beginning with ''is'' will be included.  The command,'
		PRINT '  "sp_wcProject ''sys.databases'',''name,is%,-%on''" will display the ''name'' column and all columns'
		PRINT '  starting with ''is'' except for those which end with ''on'' such as ''is_quoted_identifier_on''.'
		PRINT ''
		PRINT '- The @sqlString argument, if specified, must be either be an INITIALIZED (non-NULL) string variable'
		PRINT '  defined as VARCHAR(8000) or the string value ''P''.  If the former, the SQL command which will'
		PRINT '  generate the result set is returned in the output variable which can subsequently be used in a'
		PRINT '  more complex query, e.g., a JOIN or UNION.  If ''P'' then the SQL command string which will'
		PRINT '  generate the result set will be printed allowing manual modifications.  If omitted the query will'
		PRINT '  be run and the result set will be returned.'
		PRINT ''
		PRINT '- Note that the @sqlString argument variable must be non-NULL (an empty string is okay) and must be'
		PRINT '  qualified with the OUT attribute to receive the returned SQL SELECT statement.  See the example below.'
		PRINT ''
		PRINT '- The @orderByName argument, if specified, allows you to partially control the order of the columns in'
		PRINT '  the select statement.  Specify @orderByName=1 for alphabetical order.  Omit it or specify'
		PRINT '  @orderByName=0 for the default column ordering.' 
		PRINT ''
		PRINT '- The optional @maxWidth argument may be used to limit the displayed size of [N]CHAR and [N]VARCHAR' 
		PRINT '  strings.  If a negative value is specified then the RIGHTMOST characters of the string value which'
		PRINT '  fit in the specified maximum width are displayed.'
		PRINT ''
		PRINT '- The @maxWidth numeric value may be optionally followed by the letter "B".  (In this case you must'
		PRINT '  quote the argument, e.g. @maxWidth=''20B''.)  "B" signifies that BINARY data will be converted to'
		PRINT '  character and will, therefore, have its display size limited to the @maxWidth numeric value.'
		PRINT ''
		PRINT '- The @top numeric value, if specified, adds a TOP(@top) clause to the select statement.'
		PRINT ''
		PRINT '- You can use the optional @where and @orderBy arguments to further qualify the generated SQL.'
		PRINT ''
		PRINT 'Examples:'
		PRINT ''
		PRINT '  declare @sql VARCHAR(8000)'
		PRINT '  set @sql='''' -- MAKE SURE THE STRING IS INITIALIZED TO SOMETHING'
		PRINT '  exec sp_wcProject ''SYS.DATABASES'', ''name,database_id,reco%,%ansi%'', @sql out'
		PRINT ''
		PRINT '- returns the following sql statement in @sql:'
		PRINT ''
		PRINT '  SELECT name,database_id,recovery_model,recovery_model_desc,'
		PRINT '         is_ansi_null_default_on,is_ansi_nulls_on,is_ansi_padding_on,is_ansi_warnings_on'
	        PRINT '  FROM SYS.DATABASES'
		PRINT ''
		PRINT '==='
		PRINT ''
		PRINT '  exec sp_wcProject ''sys.databases'',''name,database_id,%desc,%ansi%,-log%'',@where=''owner_sid<>1'',@orderBy=''name'',@maxWidth=20'
		PRINT ''
		PRINT '- returns a result set like the following:'
		PRINT ''
		PRINT '  name                 database_id user_access_desc     state_desc           snapshot_isolation_state_desc recovery_model_desc  page_verify_option_desc is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on'
		PRINT '  -------------------- ----------- -------------------- -------------------- ----------------------------- -------------------- ----------------------- ----------------------- ---------------- ------------------ -------------------'
		PRINT '  AdventureWorks       7           MULTI_USER           ONLINE               OFF                           SIMPLE               CHECKSUM                0                       1                1                  1'
		PRINT '  Northwind            5           MULTI_USER           ONLINE               OFF                           SIMPLE               TORN_PAGE_DETECTION     0                       0                0                  0'
		PRINT '  Pubs                 6           MULTI_USER           ONLINE               OFF                           SIMPLE               TORN_PAGE_DETECTION     0                       0                0                  0'
		PRINT ''
		PRINT '==='
		PRINT ''
		PRINT '  exec sp_wcProject ''master.sys.sysfiles'',''%name%,%id'',@orderbyname=1'
		PRINT ''
		PRINT '- returns a result set like the following:'
		PRINT ''
		PRINT '  fileid filename                                                              groupid name'
		PRINT '  ------ --------------------------------------------------------------------- ------- --------------'
		PRINT '  1      c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf   1       master'
		PRINT '  2      c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf  0       mastlog'
		PRINT ''
		PRINT '==='
		PRINT ''
		PRINT '- to access selected columns of the PUBS log you might use something like the following:'
		PRINT ''
		PRINT '  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'''
		PRINT ''
		RETURN
	END

	-- Parse the column names and create a WHERE clause:
	DECLARE @s VARCHAR(8000), @t VARCHAR(50), @ORfilter VARCHAR(8000), @ANDfilter VARCHAR(8000), @colFilter VARCHAR(8000)
	DECLARE @i int
	DECLARE @tempTable sysname
	SET @tempTable = '##wcProject' + CAST(@@SPID AS VARCHAR(5))

	SET @ANDfilter = ''
	SET @ORfilter = ''
	SET @s = @colNames
	WHILE LEN(@s) > 0
	BEGIN
		SET @i = CHARINDEX(',',@s)
		IF @i = 0												-- Last or only token. Clear @s to end loop
			BEGIN
				SET @t = LTRIM(RTRIM(@s))
				SET @s = ''
			END
		ELSE													-- Extract the token and remove it from @s
			BEGIN
				SET @t = LTRIM(RTRIM(LEFT(@s,@i-1)))
				SET @s = RIGHT(@s,LEN(@s)-@i)
			END

		IF LEFT(@t,1) = '-' 
			SET @ANDfilter = @ANDfilter + 'name NOT LIKE ''' + SUBSTRING(@t,2,LEN(@t)-1) + ''' AND '
		ELSE 
			SET @ORfilter = @ORfilter + 'name LIKE ''' + @t + ''' OR '
	END

	IF LEN(@ANDfilter) >= 4 SET @ANDfilter = SUBSTRING(@ANDfilter,1,LEN(@ANDfilter) - 4) ELSE SET @ANDfilter = ''
	IF LEN(@ORfilter) >= 3 SET @ORfilter = '(' + SUBSTRING(@ORfilter,1,LEN(@ORfilter) - 3) + ')' ELSE SET @ORfilter = ''
	IF @ANDfilter = '' AND @ORfilter = '' 
		SET @colFilter = '1=1'
	ELSE
		--BEGIN
		IF @ANDfilter = ''										-- @ORfilter is NOT an empty string 
			SET @colFilter = @ORfilter
		ELSE 
			IF @ORfilter = ''									-- @ANDfilter is NOT an empty string 
				SET @colFilter = @ANDfilter
			ELSE
				SET @colFilter = @ORfilter + ' AND ' + @ANDfilter
		--END

	DECLARE @exec tinyint, @db sysname, @rightJust bit, @binToChar bit
	SET @rightJust = 0
	SET @binToChar = 0
	IF @sqlString IS NULL SET @exec = 1
	IF @sqlString = 'P' SET @exec = 2
	
	IF @maxWidth IS NULL 
		SET @maxWidth = 'NULL'									-- This must be the character representation for concatenation
	ELSE
		BEGIN													-- Check for leading minus or trailing "B"
			IF LEFT(@maxWidth,1) = '-'							-- Leading minus = Right justify 
				 BEGIN 
					SET @rightJust = 1 
					SET @maxWidth = SUBSTRING(@maxWidth,2,LEN(@maxWidth)-1) 
				 END
			IF RIGHT(@maxWidth,1) = 'B'							-- Trailing "B" = Convert binary to char for maxWidth
				 BEGIN 
					SET @binToChar = 1 
					SET @maxWidth = LEFT(@maxWidth,LEN(@maxWidth)-1) 
				 END
		END
	IF @maxWidth = '' SET @maxWidth = 'NULL'					-- If no numeric value then treat as if omitted

	IF OBJECT_ID('tempdb..' + @tempTable) IS NOT NULL EXEC('DROP TABLE ' + @tempTable)		-- Clean up any existing table in case
	SELECT @db = COALESCE(PARSENAME(@object,3), DB_NAME())	-- Get explicit db name or, if not specified, then use the current

	SET @sqlString = 'USE ' + @db +							-- USE @db is necessary since <db>.sys.all_columns doesn't always work as expected
		' SELECT name, column_id,' +						-- If @maxWidth = 'NULL' then max_length will be used
					' CASE WHEN type_name(system_type_id) LIKE ''%CHAR''' + 
					CASE @binToChar WHEN 1 THEN 
							  ' OR type_name(system_type_id) LIKE ''%BINARY''' ELSE '' 
					END +
							  ' THEN CASE WHEN max_length > ' + @maxWidth + ' THEN ' + @maxWidth + ' ELSE max_length END ' +
						 ' ELSE 0' +						-- Non-char are not affected
--							   The following commented code applies @maxWidth to non-character types:									
--							 ' CASE WHEN user_type_id = 61 THEN 0' +				-- DateTime	
--								  ' WHEN user_type_id = 36 THEN 0' +				-- GUID
--								  ' WHEN max_length < precision THEN precision' +	
--								  ' WHEN max_length < 5 THEN 5' +					-- Minimum column width = 5 (needed for certain types)
--								  ' ELSE 0' +
--							 ' END' +
			        ' END AS max_length' +
		' INTO ' + @tempTable + ' FROM sys.all_columns WHERE object_id = ' + 
		CAST(OBJECT_ID(@object) AS SYSNAME) +
		' ORDER BY ' + CASE @orderByName WHEN 1 THEN 'name' ELSE 'column_id' END

	IF @sqlString IS NULL									-- This shouldn't happen since an invalid object should display the help text
	BEGIN
		PRINT 'sp_wcProject: ' + @object + ' not found in ' + @db + ' database' 
		IF OBJECT_ID('tempdb..' + @tempTable) IS NOT NULL EXEC('DROP TABLE ' + @tempTable)		-- Clean up any existing table in case
		RETURN
	END

	EXEC (@sqlString)

	DECLARE @selName VARCHAR(8000)
	IF @maxWidth = 'NULL'									-- If field was NULL it was converted to a string representation
		SET @selName = 'SELECT @sql = @sql + ' + '''['' + name + ''],''' + ' FROM ' + @tempTable 
	ELSE 
		IF @rightJust = 0
			SET @selname = 'SELECT @sql = @sql + 
							CASE WHEN max_length > 0 THEN 
									''CAST(['' + name + ''] AS VARCHAR('' + CAST(max_length AS VARCHAR(5)) + '')) AS ['' + name + ''],''
								 ELSE 
									''['' + name + ''],''
							END 
							FROM ' + @tempTable
		ELSE	-- @maxWidth was negative so right justify the chars and varchars
			SET @selname = 'SELECT @sql = @sql + 
							CASE WHEN max_length > 0 THEN 
									''CAST(master.dbo.fnJustRight(['' + name + ''],'' + 
										CAST(max_length AS VARCHAR(5)) + '') AS VARCHAR('' + CAST(max_length AS VARCHAR(5)) + '')) AS ['' + name + ''],''
								 ELSE 
									''['' + name + ''],''
							END 
							FROM ' + @tempTable

	IF isNumeric(@top)=1 SET @top = 'TOP(' + @top + ')'
	SET @sqlString = 'USE ' + @db +							-- USE @db needs to get issued here if @db <> current db	
		' DECLARE @sql VARCHAR(8000) ' +
		'SET @sql = '''' ' + @selName + 
		' WHERE ' + @colFilter +
		' IF @sql = '''' PRINT ''sp_wcProject: No columns match ' + '''''' + @colNames + '''''''' + ' ELSE ' +
		' BEGIN SET @sql = ''SELECT ' + @top + ' '' + LEFT(@sql,LEN(@sql)-1) + '' FROM '' + ''' + 
		@object + @fn_args + ''' ' +
		CASE WHEN @where IS NOT NULL THEN '+ '' WHERE ' + REPLACE(@where,'''','''''') + '''' ELSE '' END +
		CASE WHEN @orderBy IS NOT NULL THEN '+ '' ORDER BY ' + REPLACE(@orderBy,'''','''''') + '''' ELSE '' END +
		CASE WHEN @exec = 1 THEN ' EXEC (@sql)' ELSE ' SELECT @cmdout=@sql' END +
		' END'

	DECLARE @parmDef NVARCHAR(500), @cmd VARCHAR(8000)
	SET @parmDef = '@cmdout VARCHAR(8000) OUTPUT'
	DECLARE @nv NVARCHAR(MAX)								-- sp_executesql requires UNICODE
	SET @nv = @sqlString
	EXECUTE sp_executesql @nv, @parmDef, @cmdout=@cmd OUTPUT
	if @exec = 2 PRINT @cmd
	set @sqlString = @cmd
GO

Rate

5 (3)

Share

Share

Rate

5 (3)