Question on following sql

Question

Question on following sql

nymgk_75

SSC Veteran

Points: 233
More actions
March 3, 2009 at 1:27 pm

#201821

I'm reviewing the procs in http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon&referringTitle=Home, and have a couple question at the sql statement below:
1) where is the tempdb..sysobjects in sqlserver manager? I see a tempdb under the 'master' db...is that created by default? I don't understand why they have two periods (dot dot) in the tempdb..sysobjects. I do not see a sysobjects table within the tempdb.
2) Can someone explain the '#' in the LIKE '#FileSpaceStats%'. The % is the wildcard, but what is the leading # used for?
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#FileSpaceStats%')
BEGIN
DROP TABLE #FileSpaceStats
END
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

beezell Hall of Fame Points: 3112 More actions · Answer 1

All good questions! I'm going to assume you're using SQL Server 2005 or 2008.

1) tempdb..sysobjects Normally you will see this written as tempdb.sys.sysobjects. This syntax is using what's known as the three part name. Broken down:

Part 1: tempdb - This is the database you are referencing

Part 2 (optional) - This is the schema you are referencing. If this part is left out, then it uses the current user's default schema (i.e. the tempdb..sysobjects refers to the current user's current schema)

Part 3: sysobjects - This is the current object you are referencing. In this case, this is a System Table that stores basic information about all of the system objects in the referenced database. To see this you will need to expand the TempDB database and expand the System Tables folder. Normally you don't need to reference system objects unless you need very low level information. Other views are in place (such as the INFORMATION_SCHEMA schema to assist with these)

2) #: The # is put in front of a table to make it a temporary table. This is a table that only hangs around until (in this case) the current user connection is closed. Once you log off the SQL Server, this table is dropped. Rather than create a permant table, the example is using the table #FileSpaceStats to store information. The IF EXISTS statement is checking the tempdb (where temporary tables will reside) in the sysobjects table to see if this table already exists. If it does, then it will drop it at this point.

Feel free to post additional questions on here as you get more familiar with SQL Server.

Cheers,

Brian

nymgk_75 SSC Veteran Points: 233 More actions · Answer 2

Awesome reply! Thanks so much...

So, the naming convention makes sense now. I haven't really worked too much with the middle part of the schema (the middle part of the naming convention). What is that? I normally see it's always dbo? Can you explain what schema is used for?

I believe I found the default schema for a user...is it under the Security /Users folder for the db, double click the user (default schema).

Thanks!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

nymgk_75 (3/3/2009)
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#FileSpaceStats%')
BEGIN
DROP TABLE #FileSpaceStats
END
Thanks

Ugh! That's actually the wrong way to do it. Here's the correct way... it doesn't have to look at system tables or information schema directly and it's a hell of a lot easier on the eye's...

--===== Conditionally drop a temp table

IF OBJECT_ID('TempDB..#FileSpaceStats','U') IS NOT NULL

DROP TABLE #FileSpaceStats

...and to add to what Brian has stated, a Temp table will also automatically drop at the end of the stored procedure that created it even if you don't log off or the session doesn't end.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

beezell Hall of Fame Points: 3112 More actions · Answer 4

Good point Jeff. I almost put the word "Scope" in there for the dropping of temp tables, but I was afraid I would overcomplicate the explaination. Good catch!

Cheers,

Brian

nymgk_75 SSC Veteran Points: 233 More actions · Answer 5

Hey Jeff,

Thanks for the reply! I like your way also...

Can you explain what the "U" is used for then? We didn't have that in the other example...

Thanks much

steveb. SSC-Forever Points: 46733 More actions · Answer 6

nymgk_75 (3/4/2009)
Hey Jeff,
Thanks for the reply! I like your way also...
Can you explain what the "U" is used for then? We didn't have that in the other example...
Thanks much

The 'U' is the object type, in this case U='User Table'

A list of all object type codes are on BOL

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

beezell (3/4/2009)
Good point Jeff. I almost put the word "Scope" in there for the dropping of temp tables, but I was afraid I would overcomplicate the explaination. Good catch!
Cheers,
Brian

Heh... "Scope".... most folks think that's a mouthwash, so I'm thinking you made a good choice, Brian. Well done. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

steveb (3/4/2009)
nymgk_75 (3/4/2009)
Hey Jeff,
Thanks for the reply! I like your way also...
Can you explain what the "U" is used for then? We didn't have that in the other example...
Thanks much
The 'U' is the object type, in this case U='User Table'
A list of all object type codes are on BOL

Steve got it right... it's actually an "undocumented" feature that works in all versions. Not sure how I got into the habit of adding it in because if the name has been used by a stored procedure instead of a table, it's not gonna prevent and error when you try to drop the table... and the only reason why I drop temp tables at the beginning is really for doing reruns when testing. It just makes life easy. They don't take but a couple of clock cycles to run so I never end up commenting them out... the drops aren't actually needed in stored procedures because the scope change of a stored procedure ending will wipe them out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

Jeff,

I got into the habit of doing it from debugging large complicated stored procedures. I copy the procedure code up to the point where I want to see intermediate results into another window in the Query Analyzer. Then run up to that point and display results. Then copy some more code and run up to that point. Since the other window isn't really a stored procedure it doesn't like trying to create a temp table that already exists.

Dropping the temp tables before creating them makes it easier since I don't have to keep explicitly dropping the temp tables before each debug run.

Todd Fifield

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Yep... that's what I do to, Todd... the "habit" I was talking about, though, is the inclusion of the 'U' in the OBJECT_ID function... it's really not necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)