sp_wcProject

-- 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