Dynamic Projection of Columns from a Query

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)

@s VARCHAR(8000),

@t VARCHAR(50),

@ORfilter VARCHAR(8000),

@ANDfilter VARCHAR(8000),

@colFilter VARCHAR(8000),

@i INT,

@tempTable SYSNAME,

@exec TINYINT,

@db SYSNAME,

@rightJust BIT,

@binToChar BIT,

@selName VARCHAR(8000),

@parmDef NVARCHAR(500),

@cmd VARCHAR(8000),

@nv NVARCHAR(MAX) -- sp_executesql requires UNICODE

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:

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

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)

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'

SET @parmDef = '@cmdout VARCHAR(8000) OUTPUT'

SET @nv = @sqlString

EXECUTE sp_executesql @nv, @parmDef, @cmdout=@cmd OUTPUT

IF @exec = 2

PRINT @cmd

SET @sqlString = @cmd

GO

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'