Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;
     
END TRY
BEGIN CATCH
END CATCH;

 

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

 

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

 

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

Transact SQL – Retrieving the structure of Temp Tables

Background

Out of laziness, I sometimes create temporary tables on the fly.

Sample

Select/Into


/*
	Create Temp Table (Scoped to Session )
*/
select 
		  [dbid] = tblSD.database_id
		, [dbname] = tblSD.[name]
		, [createDate] = tblSD.[create_date]
		, [defaultLanguage]=tblSD.default_language_name
		, [databaseOwner] = suser_sname(tblSD.owner_sid)
into #listofDatabase
from   sys.databases tblSD


 

Metadata

Let us get metadata info on our temp table columns

Outline

Checked the code for sp_help using sp_helptext ‘sp_help’ and used that stolen code here.

Code


set nocount on
set XACT_ABORT on
go

/*
	Declare Variables
*/
declare @objectNameSession sysname
declare @objectIDSession int

declare @objectNameGlobal sysname
declare @objectIDGlobal int

declare	@dbname	sysname
declare @no varchar(35)
declare @yes varchar(35)
declare @none varchar(35)
declare @precscaletypes nvarchar(150)

/*
	Init Variables
*/
set @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'
select @no = 'no', @yes = 'yes', @none = 'none'

set @objectNameSession = '#listofDatabase'
set @objectNameGlobal = '##listofDatabaseGlobal'

/*
	Clean up, if previous Temp Objects Exist
*/
if object_id('tempdb..#listofDatabase') is not null
begin

	print 'Drop table #listofDatabase'
	drop table #listofDatabase

end


if object_id('tempdb..##listofDatabaseGlobal') is not null
begin

	print 'Drop table ##listofDatabaseGlobal'
	drop table tempdb..##listofDatabaseGlobal

end


/*
	Create Temp Table (Scoped to Session )
*/
select 
		  [dbid] = tblSD.database_id
		, [dbname] = tblSD.[name]
		, [createDate] = tblSD.[create_date]
		, [defaultLanguage]=tblSD.default_language_name
		, [databaseOwner] = suser_sname(tblSD.owner_sid)
into #listofDatabase
from   sys.databases tblSD


/*
	Create Temp Table (Scoped Globally )
*/
select *
into   ##listofDatabaseGlobal
from   #listofDatabase


select @objectIDSession = ( select top 10 tblSO.id from tempdb..sysobjects tblSO where type = 'U' and name like @objectNameSession + '%' )
print '@objectIDSession ' + cast(isNull(@objectIDSession, -1) as varchar(60))

select @objectIDGlobal = ( select top 1 tblSO.id from tempdb..sysobjects tblSO where type = 'U' and name = @objectNameGlobal )
print '@objectIDGlobal ' + cast(isNull(@objectIDGlobal, -1) as varchar(60))

--exec tempdb..sp_help ##listofDatabaseGlobal
--- INFO FOR EACH COLUMN
print ' '

select
	  [tempTableScope]
		= case
			when (tblAO.name like '##%') then 'Global'
			else 'Session'
		  end
	, [objectID] = tblAO.[object_id]
	, [schema] = tblSS.[name]	
	, [objectName] = tblAO.name
	, [objectCreateDate] = tblAO.create_date

	, [columnName] = tblAC.name

	, [Type] = type_name(user_type_id)

	, [Computed] 
			= case 
				when ColumnProperty(tblAC.object_id, tblAC.name, 'IsComputed') = 0 then @no 
				else @yes 
			  end

	, [Length] 	 
			= convert(int, max_length)

	-- for prec/scale, only show for those types that have valid precision/scale
	-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
	, [Prec]	= case 
						when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
							then convert(char(5),ColumnProperty(tblAC.object_id, tblAC.name, 'precision'))
						else '     ' 
				  end

	, [Scale]  
		=  case 
				when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
					then convert(char(5),OdbcScale(system_type_id,scale))
				else '     ' 
		   end

	, [Nullable] 
		= case 
			when is_nullable = 0 then @no 
			else @yes 
		  end

	, [TrimTrailingBlanks]	
		= case ColumnProperty(tblAC.object_id, tblAC.name, 'UsesAnsiTrim')
				when 1 then @no
				when 0 then @yes
				else '(n/a)' 
		  end

	, [FixedLenNullInSource] 
			= case
				when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
					then '(n/a)'
				when is_nullable = 0 then @no else @yes 
			  end

	, [collationName] = collation_name

from [tempdb].sys.all_columns  tblAC

inner join [tempdb].sys.objects tblAO

	on tblAC.object_id = tblAO.[object_id]

inner join [tempdb].sys.schemas tblSS

	on tblAO.schema_id = tblSS.[schema_id]

where  tblAO.[type] = 'U'

and    tblAO.object_id in 
			( 
				   @objectIDSession
				 , @objectIDGlobal 
			)

order by
		tblAO.[create_date] desc



Output


Script Temp Tables

Stored Procedure

script.sp_TableTempStructure

Code



use [master]
go

if schema_id('script') is null
begin

	exec('create schema [script] authorization [dbo] ');

end
go


if object_id('[script].[sp_TableTempStructure]') is null
begin

	exec('create procedure [script].[sp_TableTempStructure] as print ''shell'' ');

end
go

alter procedure [script].[sp_TableTempStructure] 
(
	  @objectName sysname
	, @debug	bit =0
)
as
begin

	set nocount on;
	set XACT_ABORT on;
	set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	declare @objectID int
	declare @tempTableType tinyint

	declare @tblTable TABLE
	(

		  [id]					 smallint not null identity(1,1)
		, [columnID]			 smallint not null
		, [columnName]			 sysname  not null

		, [columnTypeID]		 smallint not null
		, [columnType]			 sysname  not null
		
		, [computedAsVarchar]    varchar(10) not null
		, [computedAsBit] as
		       case ( [computedAsVarchar] )
					when 'yes' then 1
					else 0
			   end		
			   
		, [length]				int not null

		, [lengthAsString] 	 
				as case
	
						when
						(
							([columnType] in ('varchar', 'nvarchar') )
						)
							then 
								case 
									when ([length] = -1) then 'max'
									else cast([length] as varchar(10))
								end

						when
						(
							([columnType] in ('numeric') )
						)
							then 
							    cast([length] as varchar(10))

						else ''

				  end
		, [precision]			int null		 			    
		, [scale]				int null		 			    

		, [nullabityAsVarchar]    varchar(10) not null
		, [nullabityAsBit] as
		       case ( [nullabityAsVarchar] )
					when 'yes' then 1
					else 0
			   end		

		, [collation]    varchar(60) null
								
		, [columnClause]
			as 
				quoteName([columnName])

					+ ' '
					+ replicate
						(
							   ' '
							 , 30 - len([columnName])

						)

					+ [columnType]	

					-- length
					+  case
							when
							(
								([columnType] in ('varchar', 'nvarchar') )
							)
								then
									 '(' 
									+ 
										case 
											when ([length] = -1) then 'max'
											else cast([length] as varchar(10))
										end
									+ ') '

							when
							(
								(
									[columnType] 
										in 
											(
												  'numeric'
												, 'float'
											) 
								)

							)
								then 
									 '(' 
									+ 
									+ cast([length] as varchar(10))
									+ ','
									+ cast
										(
											isNull
											(
												  [scale]
												, 0
											)	 
											as varchar(10))
									+ ')' 


						else ''

					  end

				  -- isNull
				  + case ( [nullabityAsVarchar] )
						when 'yes' then ' not null '
						else ' null '
					end		




	)

	
	declare @id				smallint
	declare @columnName		sysname
	declare	@idMax			smallint
	declare @columnClause	varchar(120)


	declare @no varchar(35)
	declare @yes varchar(35)
	declare @none varchar(35)
	declare @precscaletypes nvarchar(150)

	declare @sqlBuffer	nvarchar(max)

	declare @VARCHAR_SQLBUFFER_HEADER varchar(30)
	declare @VARCHAR_SQLBUFFER_FOOTER varchar(30)

	declare @CHAR_TAB	varchar(10)
	declare @CHAR_CRLF	varchar(10)
	declare @CHAR_COMMA varchar(10)
	declare @CHAR_SPACE varchar(10)

	declare @CHAR_BRACKET_OPENING char(1)
	declare @CHAR_BRACKET_CLOSING char(1)

	declare @VARCHAR_LABEL_OBJECTNOTFOUND varchar(100)

	declare @errorLog varchar(600)

	/*
		Init Variables
	*/
	set @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'
	set @no = 'no'
	set @yes = 'yes'
	set @none = 'none'

	set @CHAR_TAB = char(9)
	set @CHAR_CRLF = char(13) + char(10)
	set @CHAR_COMMA = ','
	set @CHAR_SPACE = ' '
	set @CHAR_BRACKET_OPENING = '('
	set @CHAR_BRACKET_CLOSING = ')'

	set @VARCHAR_SQLBUFFER_HEADER = 'create table '
	set @VARCHAR_SQLBUFFER_FOOTER = ' '

	set @VARCHAR_LABEL_OBJECTNOTFOUND = 'Object %s not found in Tempdb'

	if (@objectName like '##%')
	begin
		set @tempTableType = 2

		select @objectID = tblSO.[object_id]
		from   [tempdb].sys.objects tblSO
		where  tblSO.name = @objectName 

	end
	else if (@objectName like '#%')
	begin

		set @tempTableType = 1
	
		select @objectID = tblSO.[object_id]
		from   [tempdb].sys.objects tblSO
		where  tblSO.name like @objectName + '%'

	end
	else
	begin
		set @tempTableType = 0
	end


	if (@objectID is null)
	begin

		/*
		exec master.dbo.xp_sprintf
				  @errorLog output
				, @VARCHAR_LABEL_OBJECTNOTFOUND
				, @objectName

		*/
		raiserror
		(
			  @VARCHAR_LABEL_OBJECTNOTFOUND
			, 10
			,1
			, @objectName
		)

		return -1
	end


	if (@debug = 1)
	begin

		select

				tblAC.[column_id]

			,  [columnName] = tblAC.name

			, [columnTypeID]
					= tblAC.user_type_id

			, [Type] 
					= type_name(user_type_id)

			, [Computed] 
					= case 
						when ColumnProperty(tblAC.object_id, tblAC.name, 'IsComputed') = 0 then @no 
						else @yes 
					  end

			, [Length] 	 
					= convert(int, max_length)

			-- for prec/scale, only show for those types that have valid precision/scale
			-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
			, [Prec]	= case 
								when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
									then convert(char(5),ColumnProperty(tblAC.object_id, tblAC.name, 'precision'))
								else '     ' 
						  end

			, [Scale]  
				=  case 
						when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
							then convert(char(5),OdbcScale(system_type_id,scale))
						else '     ' 
				   end

			, [Nullable] 
				= case 
					when is_nullable = 0 then @no 
					else @yes 
				  end

			, [TrimTrailingBlanks]	
				= case ColumnProperty(tblAC.object_id, tblAC.name, 'UsesAnsiTrim')
						when 1 then @no
						when 0 then @yes
						else '(n/a)' 
				  end

			, [FixedLenNullInSource] 
					= case
						when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
							then '(n/a)'
						when is_nullable = 0 then @no else @yes 
					  end

			, [collationName] = collation_name

		from [tempdb].sys.all_columns  tblAC with (nolock)

		inner join [tempdb].sys.objects tblAO  with (nolock)

			on tblAC.object_id = tblAO.[object_id]

		inner join [tempdb].sys.schemas tblSS  with (nolock)

			on tblAO.schema_id = tblSS.[schema_id]

		where  tblAO.[type] = 'U'

		and    tblAO.object_id = @objectID

		order by

			tblAC.[column_id]

	end --if (@debug = 1)


	insert into @tblTable
	(

		  [columnID]
		, [columnName]

		, [columnTypeID]				
		, [columnType]			
		
		, [computedAsVarchar]   
			   
		, [length]				
		, [precision]			
		, [scale]				

		, [nullabityAsVarchar]   

		, [collation]  
	)
	select 
	      [columnID]
		  	= tblAC.[column_id]

		, [columnName] 
			= tblAC.name

		, [typeID]
			= tblAC.[user_type_id]

		, [Type] = type_name(tblAC.[user_type_id])

		, [Computed] 
				= case 
					when ColumnProperty(tblAC.object_id, tblAC.name, 'IsComputed') = 0 then @no 
					else @yes 
				  end

		, [length] 	 
				= convert
					(
						  int
						, tblAC.[max_length]
					)

		-- for prec/scale, only show for those types that have valid precision/scale
		-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
		, [precision]	= case 
							when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
								then convert(char(5),ColumnProperty(tblAC.object_id, tblAC.name, 'precision'))
							else '     ' 
					  end

		, [scale]  
			=  case 
					when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
						then convert(char(5),OdbcScale(tblAC.system_type_id, tblAC.scale))
					else '     ' 
			   end

		, [Nullable] 
			= case 
				when tblAC.is_nullable = 0 then @no 
				else @yes 
			  end

		, [collation] = tblAC.collation_name

	from [tempdb].sys.all_columns  tblAC  with (nolock)

	inner join [tempdb].sys.objects tblAO  with (nolock)

		on tblAC.object_id = tblAO.[object_id]

	inner join [tempdb].sys.schemas tblSS with (nolock)

		on tblAO.schema_id = tblSS.[schema_id]

	-- select * from [tempdb]..systypes
    inner join [tempdb].sys.types tblST  with (nolock)
	    --on tblAC.user_type_id = tblST.[xtype]
	    on tblAC.user_type_id = tblST.[user_type_id]

	where  tblAO.[type] = 'U'

	and    tblAO.object_id = @objectID

	order by
	
		tblAC.[column_id]

	set @idMax = @@ROWCOUNT

	set @id = 1
	set @sqlBuffer = ''
	while (@id <= @idMax)
	begin

		select 
			@columnClause = [columnClause]

		from @tblTable

		where [id] = @id

		set @sqlBuffer = isNull(@sqlBuffer, '')
							+ @CHAR_CRLF 
							+ @CHAR_TAB
							+ case
								when (@id = 1) then ' '
								else @CHAR_COMMA
							  end
							+ @CHAR_SPACE
							+ @columnClause

		set @id = isNull(@id, 1) + 1

	end

	if (@sqlBuffer != '')
	begin

		set @sqlBuffer = @VARCHAR_SQLBUFFER_HEADER + @objectName  + @CHAR_CRLF + @CHAR_BRACKET_OPENING + @sqlBuffer
		set @sqlBuffer = @sqlBuffer + @CHAR_CRLF + @CHAR_BRACKET_CLOSING + @CHAR_CRLF + @VARCHAR_SQLBUFFER_FOOTER

	end


	if (@debug = 1)
	begin

		select *

		from   @tblTable

	end -- if @debug =1

	print @sqlBuffer

end
go


grant execute on [script].[sp_TableTempStructure] to [public]
go


Invocation


	declare @objectName sysname

	set @objectName =  '#listofDatabase'

	exec [script].[sp_TableTempStructure]
		@objectName = @objectName

 

Output

Transact SQL – Create Object with different Structure based on runtime decisions

Background

Based on the version of SQL Server being targeted one might need to create a slightly different table structure.

Here is the original code that fails.

And, slight variations.

Implementation

Original

Code


set nocount on;
go

declare @productVersion varchar(30)
declare @productVersionMajor int

declare @iVersionMajor2012 int

set @iVersionMajor2012 = 12

set @productVersion = cast(serverproperty('ProductVersion') as varchar(30))
set @productVersionMajor = parseName(@productVersion, 4)

print '@productVersion : ' + @productVersion
print '@productVersionMajor : ' + cast(@productVersionMajor as varchar(30))


if object_id('tempdb..#LogInfo') is not null
begin

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
 
if (@productVersionMajor >= @iVersionMajor2012)
begin

	print 'Create table #LogInfo #v2012 ...'

	CREATE TABLE #LogInfo
	(
		  [RecoveryUnitID] int NULL -- Added on 20161109
		, [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 

	set noexec on

end
else
begin

	print 'Create table #LogInfo #v2012 (pre) ...'

	CREATE TABLE #LogInfo
	(
  		  [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 


end
go 

insert into #LogInfo 
EXECUTE ('DBCC LOGINFO with no_infomsgs') 

 
select
          tblSysfile.[name]
        , tblLogInfo.[FileID]
        , count(*) as cnt 
 
from #LogInfo tblLogInfo 
 
inner join sys.database_files tblSysfile 
        on tblLogInfo.FileID = tblSysfile.[file_id]
 
group by
          tblSysfile.[name]
        , tblLogInfo.[FileID]
 
order by COUNT(*) desc
 
if object_id('tempdb..#LogInfo') is not null
begin

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
go

Output


Msg 2714, Level 16, State 1, Line 53
There is already an object named '#LogInfo' in the database.
Msg 208, Level 16, State 0, Line 67
Invalid object name '#LogInfo'.


Revised ( Add Go Statement )

Premise:

We add a go to ensure object is in fact created.

Code


set nocount on;
go

declare @productVersion varchar(30)
declare @productVersionMajor int

declare @iVersionMajor2012 int

set @iVersionMajor2012 = 12

set @productVersion = cast(serverproperty('ProductVersion') as varchar(30))
set @productVersionMajor = parseName(@productVersion, 4)

print '@productVersion : ' + @productVersion
print '@productVersionMajor : ' + cast(@productVersionMajor as varchar(30))


if object_id('tempdb..#LogInfo') is not null
begin

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
 
if (@productVersionMajor >= @iVersionMajor2012)
begin

	print 'Create table #LogInfo #v2012 ...'

	CREATE TABLE #LogInfo
	(
		  [RecoveryUnitID] int NULL -- Added on 20161109
		, [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 

end

/* 
	This go is needed to ensure that object is indeed create and that object_id check succeed once object is created 
*/
go

if object_id('#LogInfo') is null
begin

	print 'Create table #LogInfo #v2012 (pre) ...'

	CREATE TABLE #LogInfo
	(
  		  [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 


end
go 

insert into #LogInfo 
EXECUTE ('DBCC LOGINFO with no_infomsgs') 

 
select
          tblSysfile.[name]
        , tblLogInfo.[FileID]
        , count(*) as cnt 
 
from #LogInfo tblLogInfo 
 
inner join sys.database_files tblSysfile 
        on tblLogInfo.FileID = tblSysfile.[file_id]
 
group by
          tblSysfile.[name]
        , tblLogInfo.[FileID]
 
order by COUNT(*) desc
 
if object_id('tempdb..#LogInfo') is not null
begin

	print 'Droping table #LogInfo ... '

	drop table #LogInfo 

end
go

Revised ( Global Table)

Premise:

If we go with a global temp table, we can enclose the creation in an exec statement.

As global temp table are available to all sessions, they will be available to the current session.

Code


use [tempdb]
go

set nocount on;
go

declare @productVersion varchar(30)
declare @productVersionMajor int

declare @iVersionMajor2012 int

set @iVersionMajor2012 = 12

set @productVersion = cast(serverproperty('ProductVersion') as varchar(30))
set @productVersionMajor = parseName(@productVersion, 4)

print '@productVersion : ' + @productVersion
print '@productVersionMajor : ' + cast(@productVersionMajor as varchar(30))


if object_id('tempdb..##LogInfo') is not null
begin

	print 'Dropping table ##LogInfo ... '

	drop table ##LogInfo 

end
 
if (@productVersionMajor >= @iVersionMajor2012)
begin

	print 'Create table #LogInfo #v2012 ...'

	exec('
	CREATE TABLE ##LogInfo
	(
		  [RecoveryUnitID] int NULL -- Added on 20161109
		, [FileID]	       BIGINT
		, [FileSize]	   BIGINT
		, [StartOffset]    BIGINT
		, [FSeqNo]         BIGINT
		, [Status]		   BIGINT
		, [Parity]		   BIGINT
		, [CreateLSN]      VARCHAR(50) 
	) 
	')

	set noexec on

end
else
begin

	print 'Create table #LogInfo #v2012 (pre) ...'

	exec('
			CREATE TABLE ##LogInfo
			(
				  [FileID]	       BIGINT
				, [FileSize]	   BIGINT
				, [StartOffset]    BIGINT
				, [FSeqNo]         BIGINT
				, [Status]		   BIGINT
				, [Parity]		   BIGINT
				, [CreateLSN]      VARCHAR(50) 
			) 
		')

end
 
set noexec off

insert into ##LogInfo 
EXECUTE ('DBCC LOGINFO with no_infomsgs') 

 
select
          tblSysfile.[name]
        , tblLogInfo.[FileID]
        , count(*) as cnt 
 
from ##LogInfo tblLogInfo 
 
inner join sys.database_files tblSysfile 
        on tblLogInfo.FileID = tblSysfile.[file_id]
 
group by
          tblSysfile.[name]
        , tblLogInfo.[FileID]
 
order by COUNT(*) desc
 
if object_id('tempdb..##LogInfo') is not null
begin

	print 'Dropping table ##LogInfo ... '

	drop table ##LogInfo 

end
go

Summary

So to get around the “Msg 2714, Level 16, State 1” or “There is already an object named #[table] in the database”, we have a couple of options.

Those options are

  1. Make sure that you have a go statement post object creation
  2. Create Global variables enclosed in an if object_id; with an exec statement