Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mb
Starting Member
16 Posts |
 Posted - 2002-05-07 : 11:18:37
|
| Does anyone know how can i see whether a temporary table exists? If I query sysobjects I cant see the table. |
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-05-07 : 11:24:36
|
| If you create a temporary table with query analyser then you will be able to see it in the sysobjects table in the tempdb database. BUT if its created in a stored procedure then it will automatically be dropped when the stored proc completes. |
 |
|
|
mb
Starting Member
16 Posts |
Posted - 2002-05-07 : 11:38:28
|
| Thanks Macka, Is there anyway I can tell if a temporary table has been created for a particular session?Maybe its better if I explain what I'm trying to do. Im trying to insert an application username into a temporary table and have triggers pick up the value and insert it into an audit table. However priviledged users may also make databases changes but will not use a temporary table. So I need to get the trigger to first check to see whether the temp table exists and if it does get the app username from it, if it doesnt then just use system_user. |
|
|
|
macka
Posting Yak Master
162 Posts |
Posted - 2002-05-07 : 11:51:31
|
| First thing to say is that if you create a temp table called #temp, it won't actually be called that in the sysobjects table. Quote from BOL:"If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters."Secondly, by the time your application has finished working with the temp table, the table won't exist for the trigger to pick up. You might want to take a look at global temporary tables - but I think you may face a similar problem. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-07 : 13:00:56
|
| If you are using an application that interfaces with your SQL Server database, you can modify its connection information to pass a Workstation ID that you can access in SQL Server using the HOST_NAME() function. That lets you avoid the temp table completely, which I honestly don't think will work properly no matter what you do.Look in Books Online under HOST_NAME() and HOST_ID() for more information. I know that ODBC under MS Access will automatically include a workstation ID with SQL Server connections, you can test this and get the proper format to use when connecting to the SQL Server. |
|
|
|
|
|
|
Seeing Temporary tables