T-SQL problem - Perl solution?

Win has asked for the wisdom of the Perl Monks concerning the following question:

My MS SQL Server 7 code snippet was something like:

If object_id(“table_name”) is not null begin … end

This does not work for MS SQL Server 2000. So I tried something along the lines of:

if (SELECT COUNT(*) FROM #Experimental_parameters) <> 0 begin DROP TABLE Experimental_parameters end

The problem is that this updated solution requires the existence of the table. So it is not the same thing as the first.

I am activating this with a Perl script. Maybe there is not a T-SQL solution for this, in which case I will need a Perl work around.

Any suggestions would be welcome, even though I know that this is not really a Perl question.

Comment on T-SQL problem - Perl solution?

Replies are listed 'Best First'.
Re: T-SQL problem - Perl solution? by EdwardG (Vicar) on Oct 25, 2004 at 11:10 UTC
The object_id() function is still available in SQL 2000 and still returns NULL if you supply the name of a non-existent table - try this example - `use pubs go if object_id('jobs') is not null print 'jobs table exists' else print 'jobs table does not exist' go --- jobs table exists` [download] A better way would be to query the database schema, rather than abuse the object_id() function. Something like this: `use pubs go if exists(select 1 from information_schema.tables where table_name = ' +jobs') print 'jobs table exists' else print 'jobs table does not exist' go` [download]	[reply] [d/l] [select]
Re^2: T-SQL problem - Perl solution? by Win (Novice) on Oct 25, 2004 at 13:52 UTC
Should the following work? `WHILE (exists(select 1 from INFORMATION_SCHEMA.tables where table_name + = 'Experimental_parameters') ...` [download]	[reply] [d/l]
Re^3: T-SQL problem - Perl solution? by monktim (Friar) on Oct 25, 2004 at 14:17 UTC
That should work Win. What's nice about using information_schema is that it's ANSI standard so it will work on compliant dBs. Your question pertains directly to T-SQL but I like to code generically when there isn't a performance hit. When I work on other dBs I have to remember less vendor specific stuff. You don't need the `SELECT 1` here. `SELECT *` with an `EXISTS` can yield internal performance optimizations with MS SQL Server. In this case it's probably trival but I thought I'd mention it.	[reply] [d/l] [select]
Re^4: T-SQL problem - Perl solution? by EdwardG (Vicar) on Oct 25, 2004 at 14:57 UTC
Re^5: T-SQL problem - Perl solution? by monktim (Friar) on Oct 25, 2004 at 15:36 UTC
Re^3: T-SQL problem - Perl solution? by EdwardG (Vicar) on Oct 25, 2004 at 13:59 UTC
That's a hard question to give a meaningful answer to; it could work in a limited set of circumstances, but I don't know your intention, or your schema for that matter. For instance, is Experimental_parameters a temporary table?	[reply]
Re: T-SQL problem - Perl solution? by mpeppler (Vicar) on Oct 25, 2004 at 11:24 UTC
Another alternative: `if exists (select * from sysobjects where type = 'U' and name = 'table +_name') begin drop table table_name end` [download] Note that you also need to take the object owne into account - the code above assumes the table is owned by 'dbo'. Michael	[reply] [d/l]
Re^2: T-SQL problem - Perl solution? by EdwardG (Vicar) on Oct 25, 2004 at 11:38 UTC
Two minor nits; This works for MSSQL 2000, but may break with MSSQL 2005 The type column exists for backward compatibility only; xtype is the right column (if you must). They are different.	[reply]
Re^3: T-SQL problem - Perl solution? by mpeppler (Vicar) on Oct 25, 2004 at 12:25 UTC
Thanks. I'm not really an MS-SQL user - I just apply the Sybase syntax, which usually works (though it looks like MS is moving in a different direction for system tables...) Michael	[reply]
Re^3: T-SQL problem - Perl solution? by gellyfish (Monsignor) on Oct 25, 2004 at 12:27 UTC
To be fair that is almost exactly what SQL Server 2000 emits in a new CREATE TABLE template if you have specified that you should create the appropriate DROP TABLE ... /J\	[reply]