Using SQL Server 2000, when I look at the name column in
tempdb..sysobjects the names of the temporary tables I have created are
always padded to the full length of the field with underscores. Is this
normal? Is this always the case on every SQL Server installation?

I am trying to test for the existence of a temporary table and because
of the padding I have to use LIKE '#TableName%' instead of using =
'#TableName'.

bill,

you need something like this:

if object_id('tempdb..#TableName') is null
    create table #TableName ...

dean

Show quoteHide quote

Nuts. I tried that before posting and it did not work. Obviously pilot
error because it works now.<g> Thanks everyone.

Bill wrote:
Temp tables (unless they are global) are owned by an individual
sesssion. The undescores and the suffix allow for multiple temp tables
with the same name to coexist in tempdb. And this would require you
knowing which object is which. Since there's no spid in the sysobjects
table in tempdb, you can't do this.

However, you can check for the existence of a temp table that is owned
by the executing session using the following script:

create table #Test (col1 int)

If object_id('tempdb..#Test') is null
  Print 'Does not exist'
Else
  Print 'Exists'

A more involved method is illustrate below:

Declare @sql nvarchar(500);
Declare @id int;
Set @sql = N'Use tempdb;Select @id = object_id(''#Test'')'
Exec sp_executesql @sql, N'@id int output', @id output
If @id is null
  Print 'Does not exist'
Else
  Print 'Exists'

drop table #Test

Thats a normal behiour, due to the fact that the tables could exists in
every different user connections they have to be (name-) extended with
some other value. if you scroll to the right to see the rest of the
name there is a number appended whcoch differs to the different
connections. You don´t have to use the Wildcard, the temp table is
detected anyway.

OBJECT_ID('tempdb..#SomeTable') should work.

HTH, Jens Suessmeyer.

To add to Dean's response, local (single '#' prefix) temp tables are made
unique so that each connection gets a different table name.  This is normal.

Show quoteHide quote

Order By Primary Key
Select where result are by the best match
where current of cursorName
Really really slow cursor
Problem copying data from column in same table