USE master
GO
IF OBJECT_ID('sp_Html') IS NULL EXEC ('CREATE PROCEDURE sp_Html AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_Html
@InputSQL VARCHAR(MAX) = NULL,
@Header VARCHAR(MAX) = NULL,
@HTML VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @InputSQL IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_Html',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
DECLARE @SQL VARCHAR(MAX),
@TempTableName VARCHAR(256) = 'TEMPDB..GetTableDefinition_' + LEFT(REPLACE(CAST(NEWID() AS VARCHAR(500)), '-', ''), 30),
@DropTempTable BIT = 1,
@Database SYSNAME
SET @Database = 'tempdb'
IF OBJECT_ID(@InputSQL) IS NOT NULL
SELECT @Database = PARSENAME(@InputSQL, 3),
@TempTableName = @InputSQL,
@DropTempTable = 0
ELSE
IF @InputSQL LIKE '#%'
AND OBJECT_ID('tempdb..' + @InputSQL) IS NOT NULL
SELECT @TempTableName = @InputSQL,
@DropTempTable = 0
ELSE
IF @InputSQL LIKE '%#%'
BEGIN
SET @SQL = ';WITH aa as(' + @InputSQL + ')
SELECT * INTO ' + @TempTableName + ' FROM aa'
EXEC(@SQL)
END
ELSE
BEGIN
SET @SQL = 'SELECT a.* INTO ' + @TempTableName + ' FROM (' + @InputSQL + ') a'
BEGIN TRY
EXEC(@SQL)
END TRY
BEGIN CATCH
SET @SQL = 'SELECT a.* INTO ' + @TempTableName + ' FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@SERVERNAME + ';DATABASE=' + DB_NAME()
+ ';Trusted_Connection=yes;'', ''' + REPLACE(@InputSQL, '''', '''''') + '
'') AS a;'
EXEC(@SQL)
END CATCH
END
EXEC dbo.sp_HtmlDetail
@Database = @Database,
@Table = @TempTableName,
@Header = @Header,
@HTML = @HTML OUTPUT
IF @DropTempTable = 1
EXEC('DROP TABLE ' + @TempTableName)
GO
EXEC sys.sp_ms_marksystemobject sp_Html
GO