Using Object_Id() Function with #Tables

using Object_id() function with #tables

Use

OBJECT_ID('tempdb..#foo')

to get the id for a temporary table when running in the context of another database.

How to get an object ID from temporary table using object_id function

Short answer...on IQ this isn't possible. Long answer...you have a few fairly good choices and some not so good choices.

1. Rewrite the entire procedure in old watcom SQL, trap the error if the operation to drop the table fails...

2. Use a permanent table (no effective difference between the 2 in IQ as far as I know)

3. Get funky...and use odd IQ behavior! If you create a temp table outside a transaction, then check @@trancount...you will get 0 as you expect. If you then open a transaction...and check @@trancount you will get 2. So...consider that a successful temp table creation :)

4. Just assume it doesn't exist on your connection :)

Sybase ASA SQL Code list:
http://manuals.sybase.com/onlinebooks/group-sas/awg0800e/dberen8/@Generic__BookTextView/334;pt=334#X

Example for #1:

DROP PROCEDURE foo;
go
create procedure foo()
begin
  DECLARE DROP_TABLE_FAILED EXCEPTION FOR SQLSTATE '42W33';

  BEGIN
    DROP TABLE T1;
    EXCEPTION 
      WHEN DROP_TABLE_FAILED 
      THEN 
      WHEN OTHERS THEN RESIGNAL;

  END;

    CREATE LOCAL TEMPORARY TABLE t1 (c1 int) 
    on commit preserve rows;

    insert into t1 select 1;

    select * from t1;

END;
go
exec foo
go
exec foo
go
drop table t1;
go

Different types of Function in Object_ID

1) IF vs multiline TF vs CLR TF

Inline Table Valued Function

Acts like macro, very efficient can be treated like parametrized view

CREATE FUNCTION dbo.name(@param INT)
RETURNS TABLE
AS 
RETURN 
   SELECT ...
   FROM tab t
   WHERE t.Col = @param;
GO

Multi Statement Table Valued Function

More flexible you can do many intermediate steps if needed but it will be slower than IF.

CREATE FUNCTION dbo.name()
RETURNS @Result TABLE
(Col_name    INT         NOT NULL,
...
)
AS
BEGIN
    /* Many operations */

    INSERT @Result
    SELECT *
    FROM ...

    RETURN
END
GO

CLR Table-Valued Functions

From MSDN

Transact-SQL table-valued functions materialize the results of calling
the function into an intermediate table. Since they use an
intermediate table, they can support constraints and unique indexes
over the results. These features can be extremely useful when large
results are returned.

In contrast, CLR table-valued functions represent a streaming
alternative. There is no requirement that the entire set of results be
materialized in a single table. The IEnumerable object returned by the
managed function is directly called by the execution plan of the query
that calls the table-valued function, and the results are consumed in
an incremental manner. This streaming model ensures that results can
be consumed immediately after the first row is available, instead of
waiting for the entire table to be populated. It is also a better
alternative if you have very large numbers of rows returned, because
they do not have to be materialized in memory as a whole. For example,
a managed table-valued function could be used to parse a text file and
return each line as a row.

2) Checking if function exists:

Check ROUTINES catalog:

Returns one row for each stored procedure and function that can be
accessed by the current user in the current database.

IF EXISTS ( SELECT  1
            FROM INFORMATION_SCHEMA.ROUTINES
            WHERE Specific_schema = 'dbo'
              AND specific_name = 'Foo'
              AND Routine_Type = 'FUNCTION' ) 

How to use variable in table name in object_id statement in a while-loop in sql

Well I dont know what SQL your using MSSQL or MYSQL, or other.

This syntax I am unfamiliar with what: '[a\b].rot@t' so I put a note below you may have to do that differently as I am not familiar with how that is working.

But syntax may be different but you can do something like this:

DECLARE @DynamicSQL varchar(MAX) = ''
declare @t int = 10
while @t <=11
begin
  if object_id('[a\b].rot@t'. 'U') is null

-- here instead of doing drop directly build out a string:

--drop table [a\b].rot@t  dont do this 
-- though I am not familar with this syntax of the @t in the string, so you may have to concat that with the drop table some other way
SET @DynamicSQL = 'drop table [a\b].rot@t'  
-- then execute the dynamic SQL variable
EXEC @DynamicSQL
  set @t = @t + 1
end

Why can I not find a foreign key using the OBJECT_ID() function?

Well it could be that your foreign key is looking to the table not in default schema (probably dbo). In this case you'll not see object_id until you specify schema, like this:

SELECT OBJECT_ID(N'<schema>.FK_Name', N'F')

Actually, you could have multiple objects with the same name in your database, but within different schemas. OBJECT_ID(N'FK_Name', N'F') will return id of object in the default schema.

You can test it like this:

create schema test
create table test.temp1 (id int primary key)
create table test.temp2 (id int)
go

alter table test.temp2 add constraint FK_temp foreign key(id) references test.temp1(id)

select object_id('FK_temp', 'F')  -- returns null
select object_id('test.FK_temp', 'F') -- returns object id

drop table test.temp2
drop table test.temp1
drop schema test

sql fiddle demo

---

Related Topics