January 11, 2008 at 6:31 pm
In SQL 2000, there is a system table called sysobjects that contains table name in a database, which gives us info about a table existed or not. In SQL 2005, the system table is no there anymore. Is there a better way in SQL 2005 to detect table existed, ideally, from C# code?
January 11, 2008 at 7:36 pm
Even in SQL Server 2000, there is no need to engage any systems tables to figure out if an object exists...
IF OBJECT_ID('someobjectname','xtype') IS NOT NULL
... object exists ...
ELSE
... object does not exist ...
--Jeff Moden
January 14, 2008 at 4:48 pm
Thank you Jeff.
If the table name is already known when one designs the stored procedure, using T-SQL's IF ELSE would do the job.
However, in my case, the table name string is dynamically created and passed from an C# application and changed daily. As far as I know (I may be wrong), T-SQL based stored procedures would not take a table name as an input string parameter. Therefore, instead of using T-SQL based stored procedure, I had to do some coding in the C# side of the application. Using SQL 2000 system tables' info is not neat but did the job using C#. That is why I have the difficulty on SQL 2005. Do I miss something here?
January 14, 2008 at 5:30 pm
Yes... use "Dynamic SQL" to accomplish this...
--Jeff Moden
January 14, 2008 at 11:39 pm
DECLARE @TblName VARCHAR(50)
SET @TblName = 'MyTable' -- can be a parameter also
IF (SELECT OBJECT_ID(@TblName,'u')) IS NOT NULL
PRINT 'Table Exists'
ELSE
PRINT 'Table Not Exists'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2008 at 11:55 pm
sys.objects or information_schema.tables
"Keep Trying"
January 15, 2008 at 2:13 am
--1. using function of object_id
if object_id('object_name') is not null begin
print 'Exist'
end else begin
print 'Not exist'
end
--2. using sys.objects & exists function
if exists(select 1 from sys.objects where name 'object_name'') begin
print 'exists'
end else begi
print 'not exists'
end
--3. using stored procedures
-- ex) sp_tables, sp_stored_procedures
exec dbo.sp_tables 'table_name'
if @@rowcount > 0 begin
print 'exists'
end else begin
print 'not exists'
edn
January 15, 2008 at 5:09 am
Jeff Moden (1/14/2008)
Yes... use "Dynamic SQL" to accomplish this...
Ack... not enough coffee... sorry.
Gail's (GilaMonster) post is correct for this...
--Jeff Moden
October 16, 2008 at 1:45 am
Use the script below
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 16, 2008 at 7:19 pm
krayknot (10/16/2008)
Use the script belowIF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'
Why would you query a view when the system function "OBJECT_ID" that Gail used works?
--Jeff Moden
October 16, 2008 at 7:30 pm
or - just build the table using a predefined name, and use sp_rename. No dynamic SQL that way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply