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

 

SQL Server – Transact SQL – Script Foreign Keys

Background

Looking at a slow query and found out that one of its main table is a Heap.  And, so knew that that it needed to be recreated as a Clustered Table.

As an aside, the Internet has good discussions around SQL Server’s Clustered Table and Index Organized Tables ( IOT ) in Oracle, but that will have to wait another day.

Index – Recreation

Regular Index

Create Table And Non Clustered Index

If just a regular index:

Code:

Let us create a table and add a non-clustered index.


use [tempdb]
go

if object_id('dbo.tblHeap2015Nov') is not null
begin

  drop table dbo.tblHeap2015Nov;

end
go

create table [dbo].[tblHeap2015Nov]
(
	[id] bigint
)
go

create index [INDX_ID]
on [dbo].[tblHeap2015Nov]
( 
	[id]
)
go

exec sp_helpindex '[dbo].[tblHeap2015Nov]'
go


Output:

normalIndex

Convert Non-Clustered Index To Clustered Index

Let us change the index from non-clustered to Clustered.


create clustered index [INDX_ID]
on [dbo].[tblHeap2015Nov]
( 
	[id]
)
with
	(
		DROP_EXISTING = ON
	)
go

exec sp_helpindex '[dbo].[tblHeap2015Nov]'
go

Output:

normalIndexConvertedToClustered

Heap \ Primary Key

Create Heap (with a non clustered primary key )

Let us create another table, but this time add a non clustered primary key

Code:


use [tempdb]
go

if object_id('dbo.tblHeapPK2015Nov') is not null
begin

   drop table dbo.tblHeapPK2015Nov;

end
go

create table [dbo].[tblHeapPK2015Nov]
(

    [id] bigint

    , constraint [PK_HEAPPK2015NOV] PRIMARY KEY NONCLUSTERED 
	(
		[id]
	)

)
go

exec sp_pkeys 
		  @table_owner = 'dbo'
		, @table_name =  'tblHeapPK2015Nov'
go

exec sp_helpindex '[dbo].[tblHeapPK2015Nov]'
go


Output:

NonClusteredPrimaryKey

Change Non-Clustered Index to Clustered


create clustered index [PK_HEAPPK2015NOV]
on [dbo].[tblHeapPK2015Nov]
( 
  [id]
)
with
(
   DROP_EXISTING = ON
)
go


Output (Text ):


Msg 1907, Level 16, State 1, Line 4
Cannot recreate index 'PK_HEAPPK2015NOV'. The new index definition does not match the constraint being enforced by the existing index.

Output ( Image ):

NewIndexDefinitionDoesNotMatchTheConstraint

Alter Primary Key

If we try to alter primary key …


alter table [dbo].[tblHeapPK2015Nov]
	alter constraint PK_HEAPPK2015NOV
   ......

We will find out, we can not syntactically to do so…

Modify Primary Keys
https://msdn.microsoft.com/en-us/library/ms189251.aspx#TsqlProcedure

To modify a PRIMARY KEY constraint using Transact-SQL:

a) You must first delete the existing PRIMARY KEY constraint

b) And, then re-create it with the new definition

Drop Primary Keys

Code

Here is a sample script for dropping a primary key on a table.


begin tran

  alter table [worktable].[person]
   drop constraint [PK_WORKTABLE_PERSON]
	
rollback tran

Output:

If the targeted Primary Key is referenced by foreign key constraints, we get the error message listed below:

Image:

dropPrimaryKeyErrorWhenReferenced

Textual:

Msg 3725, Level 16, State 0, Line 4

The constraint 'PK_WORKTABLE_PERSON' is being referenced by
table 'personAttribute', foreign key constraint 'FK_WORKTABLE_PERSON'.

Msg 3727, Level 16, State 0, Line 4
Could not drop constraint. See previous errors.

Script Constraints

We see that we likely have to drop and re-create the primary key.

BTW, this applies to all constraints, not just primary keys.

If there are foreign keys referencing the Primary Key, we have to …

  1. Script dependent Foreign Keys
  2. Script the Primary Keys
  3. Drop the Foreign Keys
  4. Drop the Primary Key
  5. Recreate the Primary Key
    • In our case, create as a clustered index
  6. Recreate the referencing Foreign keys

Code

Alan’s Script

Here is a nice script from Alan at Grapefruitmoon


SELECT
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
        + ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
    'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
    + ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id) 
        + ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id) 
        + '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
FROM
    sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id

Revised Script

We modified Alan’s script the following ways:

  1. Added support for composite keys
    • Without it, we will get the following error:6, Level 16, State 0, Line 5
      There are no primary or candidate keys in the referenced table ‘dbo.ReferencedTable’ that match the referencing column list in the foreign key ‘FK_ReferencingTable_ReferencedTable‘.

use master
go
 
if object_id('dbo.sp_ScriptForeignKeys') is null
begin
 
    exec('create procedure [dbo].[sp_ScriptForeignKeys] as select 1/0 as [shell] ')
     
end
go
 
alter procedure dbo.sp_ScriptForeignKeys
(
      @schema			sysname = null
    , @object			sysname = null
	, @includeDisabled  bit = 0
	, @scriptText	    tinyint = 0
)
as

	set nocount on;

	declare @CHAR_CRLF varchar(30);
	declare @CHAR_COMMAND_SEPARATOR varchar(30);

	set @CHAR_CRLF = char(13) + char(10);
	set @CHAR_COMMAND_SEPARATOR = 'go'

	declare @script TABLE
	(
		  [id]					int not null identity(1,1)

		, [table]				sysname not null

		, [constraint]			sysname not null

		, [isDisabled]			bit 

		, [dropStatement]		varchar(600) not null
		, [createStatement]		varchar(600) not null
		, [enableStatement]		varchar(600) not null
		, [disableStatement]	varchar(600) not null


	)
    ; with ForeignKeyColumnsParent
    as
    (
 
        SELECT
 
                tblFKC.constraint_object_id
 
            , STUFF
                (
                    (
 
                        SELECT ', '
                            + QuoteName(tblSC.name)
 
                        from sys.foreign_key_columns tblFKC_Inner
                         
                        inner join sys.columns tblSC            
 
                        on    tblFKC_Inner.parent_object_id
                                    = tblSC.[object_id]
 
                        and   tblFKC_Inner.parent_column_id 
                                = tblSC.column_id 
 
                        and   tblFKC.constraint_object_id 
                                = tblFKC_Inner.constraint_object_id
 
                        ORDER BY
                            tblFKC_Inner.constraint_column_id
 
                        FOR XML PATH('')
                    )
                    , 1
                    ,1
                    ,''
                ) as columnList
 
 
        from   sys.foreign_key_columns tblFKC
 
        group by
                tblFKC.constraint_object_id
 
    )
    , ForeignKeyColumnsReferenced
    as
    (
 
        SELECT
 
                tblFKC.constraint_object_id
 
            , STUFF
                (
                    (
 
                        SELECT ', '
                                + QuoteName(tblSC.name)
 
                        from sys.foreign_key_columns tblFKC_Inner
                         
                        inner join sys.columns tblSC            
 
                        on    tblFKC_Inner.referenced_object_id
                                    = tblSC.[object_id]
 
                        and   tblFKC_Inner.referenced_column_id 
                                = tblSC.column_id 
 
                        and   tblFKC.constraint_object_id 
                                = tblFKC_Inner.constraint_object_id
 
                        ORDER BY
                            tblFKC_Inner.constraint_column_id
 
                        FOR XML PATH('')
                    )
                    , 1
                    ,1
                    ,''
                ) as columnList
 
 
        from   sys.foreign_key_columns tblFKC
 
        group by
                tblFKC.constraint_object_id
    )
 
	insert into @script
	(
		  [table]

		, [constraint]

		, [isDisabled]

		, [dropStatement]	
		, [createStatement]	

		, [disableStatement]
		, [enableStatement]	

	)
    SELECT
 
          [table]
            = quoteName(ss.name)
                + '.'
                + quoteName(t.name)
 
        , [constraint]
            = QuoteName(fk.NAME)

		, [isDisabled]
			= fk.is_disabled
 
        , DropStatement
            = 
				  ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NOT NULL) '
				 + ' begin '
				 + 'ALTER TABLE '
                 + QuoteName(s.name) 
                 + '.'
                 + QuoteName
                    (
                        OBJECT_NAME(fk.parent_object_id)
                    )
				+ ' DROP CONSTRAINT '
				+ QuoteName(fk.NAME)
				+ ' ;'
 				+ ' end '
				+ @CHAR_CRLF

        , CreateStatement
            = 
				  ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NULL) '
				 + ' begin '

				 + 'ALTER TABLE '
				 + QuoteName(s.name) + '.'
				 + QuoteName(OBJECT_NAME(fk.parent_object_id))
				 + ' ADD CONSTRAINT '
				 + QuoteName(fk.NAME)
				 + ' FOREIGN KEY ('
				 + cteFKCP.[columnList]
				 + ') '
				 + ' REFERENCES '
				 + QuoteName(ss.name )
				 + '.'
				 + QuoteName
					(
						OBJECT_NAME(fk.referenced_object_id) 
					)
				 + '('
				 + cteFKCR.[columnList]
				 + ');'
				 + ' end '
				 + @CHAR_CRLF

        , DisableStatement
            = ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NOT NULL) '
				 + ' begin '
				 + ' ALTER TABLE '
                 + QuoteName(s.name) 
                 + '.'
                 + QuoteName
                    (
                        OBJECT_NAME(fk.parent_object_id)
                    )
				+ ' NOCHECK CONSTRAINT '
				+ QuoteName(fk.NAME)
				+ ' ;'
 				+ ' end '
				+ @CHAR_CRLF

        , EnableStatement
            = ' IF (OBJECT_ID(' 
				 + ''''
				 + QuoteName(s.name) + '.'
				 + QuoteName(fk.name)
				 + ''''
				 + ', ''F'') IS NOT NULL) '
				 + ' begin '
                 +  'ALTER TABLE '
                 + QuoteName(s.name) 
                 + '.'
                 + QuoteName
                    (
                        OBJECT_NAME(fk.parent_object_id)
                    )
            + ' WITH CHECK CHECK CONSTRAINT '
            + QuoteName(fk.NAME)
            + ' ;'
			+ ' end '
			+ @CHAR_CRLF
 
    FROM  sys.foreign_keys fk
 
    INNER JOIN ForeignKeyColumnsParent cteFKCP
        ON fk.object_id = cteFKCP.constraint_object_id
 
    INNER JOIN ForeignKeyColumnsReferenced cteFKCR
        ON fk.object_id = cteFKCR.constraint_object_id
 
    INNER JOIN sys.schemas s 
        ON fk.schema_id = s.schema_id
 
    INNER JOIN sys.tables t 
        ON fk.referenced_object_id = t.object_id
 
    INNER JOIN sys.schemas ss 
        ON t.schema_id = ss.schema_id
 
    WHERE ss.name = isNull(@schema, ss.name)
  
    and   fk.referenced_object_id
			= isNull
				(
					  object_id(@object)
					, fk.referenced_object_id
				 )  

	and  fk.is_disabled = case
								when (isNull(@includeDisabled, 0) =0) then 0
								when (@includeDisabled =1) then fk.is_disabled
						  end	
 
    order by 1, 2      
	
	if (
			   (@scriptText is null )
			or (@scriptText < 1 )
		)
	begin
	
		select *
		from   @script

	end
	else if (@scriptText = 1 )
	begin

		select [dropStatement] + @CHAR_COMMAND_SEPARATOR  + @CHAR_CRLF 	
		from   @script

	end
	else if (@scriptText = 2 )
	begin

		select [createStatement]  + @CHAR_COMMAND_SEPARATOR  + @CHAR_CRLF 		
		from   @script

	end
	else if (@scriptText = 3 )
	begin

		select [disableStatement]  + @CHAR_COMMAND_SEPARATOR + @CHAR_CRLF 	
		from   @script

	end
	else if (@scriptText = 4 )
	begin

		select [enableStatement]  + @CHAR_COMMAND_SEPARATOR  + @CHAR_CRLF 		
		from   @script

	end
	             
go
 
grant execute on dbo.sp_ScriptForeignKeys to [public]
go
 
EXEC sys.sp_MS_marksystemobject 'dbo.sp_ScriptForeignKeys'
go

Query Plan

Btw, here is a quick comparison between Heaps and Clustered Indexes when the key is searched on.

Heap

Query Plan

heap

Statistics IO

heapStatisticsIO

Clustered Index

Query Plan

ClusteredIndex

Statistics IO

ClusteredIndexStatisticsIO

Explanation

  1. When the Primary Key is an heap
    1. SQL Server accesses both the index and uses the Row ID (RID) to fetch any other data that is not included in the Primary Key
  2. On the other hand, when the Primary Key is a Clustered Index
    1. Only the Clustered Index is accessed, as it contains all data

Summary

In summary, there are two types of indexes in SQL Server.

There are explicitly created indexes defined using “Create Index”.

And, there are implicitly defined ones created using “ALTER TABLE – ADD CONSTRAINT PRIMARY KEY” or “ALTER TABLE – ADD UNIQUE CONSTRAINT

Existing indexes can be modified using DROP_EXISTING. i.e.

DROP_EXISTING = { ON | OFF }

Specifies that the named index is dropped and rebuilt.

On the other hand, constraints definitions are immutable.

And, so prior to re-creating Primary Key constraints and Candidate Keys, we have to first remove dependent FK constraints.

SQL Server – Get Primary Key Columns

Using sp_help

Syntax:


   exec sp_help 'object-name'

Sample:


   exec sp_help 'dbo.Customer'

Table -- sp_help (v2)

Explanation:

  • Check the RowGuidColumn section
  • Check the Index section and look for any entries bearing “Primary Key”
  • Check the Constrain section and look for any entries bearing “PRIMARY KEY”

Using sp_helpindex

Syntax:


   exec sp_helpindex 'schema.table-name'

Sample:


   exec sp_helpindex 'dbo.customer' 

Output:

Table -- sp_helpindex

Using sp_pkeys

Syntax:


   exec sp_pkeys 'object-name', 'schema-name'

Sample:


   exec sp_pkeys 'Customer', 'dbo'

Output:

Table -- sp_pkeys

 

 

sp_primary_keys

Not aware of this SP until reading SQL Server: Get table primary key using sql query [duplicate] ( http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query ).


Syntax:


     declare @serverName sysname
     declare @databaseName sysname
     declare @is_data_access_enabled bit

     set @serverName = CAST(serverproperty('servername') as sysname)
     set @databaseName = db_name()  

     select @is_data_access_enabled = [is_data_access_enabled] 
     from   sys.servers
     where  name = @serverName
     and    is_data_access_enabled = 0

     if  (
             (@is_data_access_enabled is null) 
          or (@is_data_access_enabled = 0) 
         )    
     begin

        print 'Setting ServerOption:'
        print '
            Msg 7411, Level 16, State 1, Procedure sp_primarykeys, Line 10
            Server  is not configured for DATA ACCESS.
             '

       exec sp_serveroption @serverName 
                            , 'data access'
                            , 'true' --execute once  

    end

   exec sp_primarykeys
          @table_server = @serverName
        , @table_catalog = @databaseName 
        , @table_schema = 'dbo'
        , @table_name = 'customer' 


Sample:


     declare @serverName sysname
     declare @databaseName sysname
     declare @is_data_access_enabled bit

     set @serverName = CAST(serverproperty('servername') as sysname)
     set @databaseName = db_name()  

     select @is_data_access_enabled = [is_data_access_enabled] 
     from   sys.servers
     where  name = @serverName
     and    is_data_access_enabled = 0

     if  
     (
             (@is_data_access_enabled is null) 
          or (@is_data_access_enabled = 0) 
      )    
      begin

         print 'Setting ServerOption:'
         print '
            Msg 7411, Level 16, State 1, Procedure sp_primarykeys, Line 10
                Server  is not configured for DATA ACCESS.
              '

         exec sp_serveroption 
                  @serverName 
                , 'data access'
                , 'true' --execute once  

end

exec sp_primarykeys
          @table_server = @serverName
        , @table_catalog = @databaseName 
        , @table_schema = 'dbo'
        , @table_name = 'customer'

Output:

sp_primary_keys

Explanation:

  • The sp_primarykeys SP is quite straightforward
  • But, the ‘data access’ server option needs to be turned on

 

Using INFORMATIONAL_SCHEMA


Syntax:

   --http://stackoverflow.com/questions/3930338/
   --     sql-server-get-table-primary-key-using-sql-query   
   SELECT 
              tblTableKey.CONSTRAINT_SCHEMA
            , tblTableKey.TABLE_NAME
            , tblTableKey.column_name
            , tblTableKey.ORDINAL_POSITION
   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE tblTableKey
   WHERE  OBJECTPROPERTY(OBJECT_ID(tblTableKey.constraint_name)
                           , 'IsPrimaryKey') = 1
   AND    tblTableKey.CONSTRAINT_SCHEMA = 
   AND    tblTableKey.table_name = 
   ORDER  by tblTableKey.ORDINAL_POSITION asc
   ;

Sample:


   SELECT 
              tblTableKey.CONSTRAINT_SCHEMA
            , tblTableKey.TABLE_NAME
            , tblTableKey.column_name
            , tblTableKey.ORDINAL_POSITION
   FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE tblTableKey
   WHERE  OBJECTPROPERTY(OBJECT_ID(tblTableKey.constraint_name)
                         , 'IsPrimaryKey') = 1
   AND    tblTableKey.CONSTRAINT_SCHEMA = 'dbo'
   AND    tblTableKey.table_name = 'customer'
   ORDER  by tblTableKey.ORDINAL_POSITION asc
   ;

Table -- Informational_Schema

References: