Menu
I want to ensure if a temporary table exists in my database or not.
I tried to use OBJECT_ID() function but it seems that I can't use it with temporary tables.
How can I resolve this problem?
697
T-SQL OBJECT_ID() metadata function This function returns the database object ID number of a schema object and returns NULL on error. These objects can be tables, views, constraints or stored procedures etc.
objects catalog view for the object specified by OBJECT_NAME in the WHERE clause of the SELECT statement. (Your object number (274100017 in the example below) will be different. To test this example, look up a valid object number by executing SELECT name, object_id FROM sys. objects; in your database.)
Use SQL Server Management StudioIn Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance. To see a list of all databases on the instance, expand Databases.
Return the ID of the current database. This example returns the database ID of the current database. SELECT DB_ID();
Use
OBJECT_ID('tempdb..#foo') to get the id for a temporary table when running in the context of another database.
175
When OBJECT_ID is called, for Temporary table/Hash table TEMPDB it must be specified unless it is already working database.
I check in SQL2008 and verify below.
USE SampleDB create table #tt1 (dummy int) select OBJECT_ID('SampleDB..#tt1') -- returns NULL select OBJECT_ID('tempdb..#tt1') -- returns ID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
