NuRoN Consulting, INC.

Blog > SQL Tools > Template Scripts > System Proc > sp_HtmlDetail

Template Script: System Proc\sp_HtmlDetail.sql

USE MASTER
GO
IF OBJECT_ID('dbo.sp_HtmlDetail') IS NULL
    EXEC ('CREATE PROCEDURE dbo.sp_HtmlDetail AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_HtmlDetail
    @Database SYSNAME,
    @Table SYSNAME,
    @Header VARCHAR(MAX) = NULL,
    @HTML VARCHAR(MAX) OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @HeaderStr VARCHAR(MAX),
    @Detail NVARCHAR(MAX),
    @DetailValues VARCHAR(MAX)

CREATE TABLE #COLUMNS ([SchemaName] SYSNAME NOT NULL,
                       [TableName] SYSNAME NOT NULL,
                       [ColumnName] SYSNAME NULL,
                       [TypeName] SYSNAME NOT NULL)

EXEC('INSERT #COLUMNS
SELECT
    sc.name AS SchemaName,
    tb.Name AS TableName,
    co.Name AS ColumnName,
ts.name AS TypeName
FROM      ' + @Database + '.sys.schemas sc (NOLOCK)
INNER JOIN ' + @Database + '.sys.Tables tb (NOLOCK) ON sc.schema_id = tb.schema_id
INNER JOIN ' + @Database + '.sys.columns co (NOLOCK) ON co.object_id = tb.object_id
INNER JOIN ' + @Database + '.sys.types ts (NOLOCK) ON ts.user_type_id = co.user_type_id
WHERE     tb.object_id = OBJECT_ID(''' + @Table + ''')')

SELECT  @HeaderStr = '

' + ISNULL(@Header, SchemaName + '.' + TableName) + '

' + Util.dbo.StringConcat('  ' + ColumnName + ' ', '
') + '
',
        @Detail = 'SELECT @DetailValues = Util.dbo.StringConcat(''
' + Util.dbo.StringConcat('  '' + ISNULL(' + CASE WHEN TypeName LIKE '%char%' THEN '[' + ColumnName + ']'
                                                             ELSE 'CAST([' + ColumnName + '] AS VARCHAR)'
                                                        END + ', '''') COLLATE SQL_Latin1_General_CP1_CI_AS + ''',
                                 '
') + '
'', '''')
FROM ' + @Database + '.' + SchemaName + '.' + TableName
FROM    #COLUMNS
GROUP BY SchemaName,
        TableName

EXEC sp_executesql
    @Detail,
    N'@DetailValues VARCHAR(MAX) OUTPUT',
    @DetailValues OUTPUT

SELECT  @HTML = @HeaderStr + @DetailValues + '

'
GO
EXEC sys.sp_ms_marksystemobject
    sp_HtmlDetail
GO

Description for Template Script: System Proc\sp_HtmlDetail.sql

Todo