Display results VERTICALLY in Results window

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/********************************************************************************************************
** NAME: sp_ShowDown
**
** DESC: Display (SHOWs) the results of a SELECT vertically (DOWN) instead of horizontally
** The query can be as complex as necessary with as many joins however the column names
** must be unique because of the temp table.  Note image and text fields display only 
** their size (DATALENGTH)
**
** PARM: @Help = 1 will display syntax and instructions
**
** RETR: the resultset of the records.  Notes displaying a lot of records will take a LONG time.  Generally
** this should be used for recordsets of no more than 10.  
**
** AUTH: D. Simmons
**
** SYNTAX  sp_showdown 1 -- displays full syntax on how to run
**
** MOD DATE:
** 05.22.07 - DTS Prevent casting of image & text to varchar
** 05.20.07 - DTS original version
*********************************************************************************************************/ALTER PROCEDURE [dbo].[sp_ShowDown] (
@helpBIT = NULL
)
AS

SET NOCOUNT ON

-- ------------------------------------------------------------------------
-- DECLARATION AND TABLE CREATION
-- ------------------------------------------------------------------------

DECLARE 
@ColumnVARCHAR(60),-- the fieldname
@CurrOrdPosINT,-- the order of the column in the table
@SQLVARCHAR(1000),-- dynamic select statement
@SQCHAR(1),-- single quote
@MaxTableVARCHAR(1000),-- holds the tempwide2 name - the true one stored in tempdb
@RecordIDINT,-- each record's number to aid in sorting when more than one record is return
@DataTypeVARCHAR(25),-- the datatype of the field
@FieldNameVARCHAR(200)-- will hold column's name with brackets ready for the SELECT

IF OBJECT_ID('tempdb..#tempdown') IS NOT NULL DROP TABLE #tempdown

CREATE TABLE #tempdown (
RecINT,-- short column names on purpose so it doesn't take up much 
OrdINT,-- space in final result
ColumnNameVARCHAR(60),-- the columnname 
DataVARCHAR(7500)-- the data for the column
)

-- ------------------------------------------------------------------------
-- INITIALIZE
-- ------------------------------------------------------------------------

SET @RecordID = 0

-- CONSTANTS
SET @SQ = CHAR(39)-- single quote


-- ------------------------------------------------------------------------
-- LOGIC
-- ------------------------------------------------------------------------ 

-- print the syntax and usage instructions to the result window
IF @Help = 1 BEGIN
PRINT 'Keep in mind that with temp tables the column names must be unique!'
PRINT ' '
PRINT 'Example of syntax: '
PRINT ' '
PRINT 'IF OBJECT_ID(''tempdb..#tempwide'') IS NOT NULL DROP TABLE #tempwide  -- ADD TO TOP OF YOUR SELECT'
PRINT ' '
PRINT 'SELECT TOP 1 * '
PRINT 'INTO#tempwide-- ADD THIS TO YOUR QUERY'
PRINT 'FROMauthors a'
PRINT ' ' 
PRINT 'EXEC _SHOWDOWN-- ADD AS THE LAST LINE'
PRINT '  '   
PRINT 'COPY THESE LINES and place where instructed'
PRINT 'IF OBJECT_ID(''tempdb..#tempwide'') IS NOT NULL DROP TABLE #tempwide'
PRINT 'INTO #tempwide'
PRINT 'EXEC sp_SHOWDOWN'

RETURN
END

-- Create a new 'wide' table so we can add a RecordID (DIDROCER) which allows muliple records and their fields 
-- to be grouped together.  DIDROCER is RecordID backwards.  Needed a field name that will have an unlikely
-- chance of ever being in a real table since it will be excluded from the results displayed vertically.
SELECT0 'DIDROCER', *
INTO#tempwide2
FROM#tempwide

-- increment the record id for the table
UPDATE#tempwide2 SET@RecordID = DIDROCER = @RecordID + 1

-- get name of tempwide2 table (the true name in tempdb)
SET @MaxTable = (SELECTMAX(TABLE_NAME) 
FROMtempdb.INFORMATION_SCHEMA.TABLES
WHERETable_Name LIKE '%#tempwide2%'
)

-- get the min ord position for the first column for my temp table.  Eliminates need for cursor
SET @CurrOrdPos = ( SELECTMIN(Ordinal_Position) 
FROMtempdb.INFORMATION_SCHEMA.COLUMNS 
WHERETable_Name LIKE '%' + @MaxTable + '%' )


-- while we have columns in the temp table loop through them and put their data into the 
-- tempdown table
WHILE @CurrOrdPos IS NOT NULL BEGIN 

-- get a column name and the data type
SELECT@Column = COLUMN_NAME, @DataType = Data_Type
FROMtempdb.INFORMATION_SCHEMA.COLUMNS 
WHERETable_Name LIKE '%' + @MaxTable + '%' 
ANDOrdinal_Position = @CurrOrdPos 


IF @Column <> 'DIDROCER' BEGIN-- if it is not the recordid (spelled backward) row from tempwide2 get the row


IF @DataType IN ( 'image', 'text' ) BEGIN
-- 'Size of Data: ' + CONVERT(VARCHAR(15), DATALENGTH([NoteText] )) 
SET @FieldName = @SQ + 'Size of Data: ' + @SQ + ' + CONVERT(VARCHAR(15), DATALENGTH(' + @FieldName + ')) '
END ELSE BEGIN
SET @FieldName = 'CAST( [' + @Column + '] AS VARCHAR(7500) )'-- the fieldname w/ brackets used in SELECT to display the data
END

-- build the insert that will put the data into the tempdown table
SET @SQL = ' INSERT INTO #tempdown ' 
SET @SQL = @SQL + 'SELECT didrocer ' + @SQ + 'RecordID' + @SQ + ', '-- recordid field from tempwide2 table
SET @SQL = @SQL + CONVERT(VARCHAR(10), @CurrOrdPos) + ', '-- order of the column
SET @SQL = @SQL + @SQ + @Column + @SQ + ' ' + @SQ + 'Field' + @SQ + ', '-- field name 
SET @SQL = @SQL + @FieldName + @SQ + @Column + @SQ-- field data
SET @SQL = @SQL + ' FROM ' + @MaxTable-- from tempwide2
END

--@SQL above looks like this:
--INSERT INTO #tempdown SELECT DIDROCER 'RecordID', 5, 'UserID' 'Field', [UserID] 'UserID' FROM #tempwide2 {shorten}_____00010000003F
--PRINT @SQL

EXEC ( @SQL )-- run the insert into #tempdown

-- get the next column pos
SET @CurrOrdPos = ( SELECTMIN(Ordinal_Position) 
FROMtempdb.INFORMATION_SCHEMA.COLUMNS 
WHERETable_Name LIKE '%' + @MaxTable + '%'
AND Ordinal_Position > @CurrOrdPos)


END

-- display the results VERTICALLY!
SELECTColumnName, Data FROM#tempdown ORDER BY Rec, Ord, ColumnName

-- clean up
IF OBJECT_ID('tempdb..#tempdown') IS NOT NULL DROP TABLE #tempdown
IF OBJECT_ID('tempdb..#tempwide') IS NOT NULL DROP TABLE #tempwide
IF OBJECT_ID('tempdb..#tempwide2') IS NOT NULL DROP TABLE #tempwide2