How to determine if table exist using Transact SQL

[WIREMESH]

Using Transact SQL How can I determine if a table exists in a SQL Server database. If it exists, I want to use the ALTER TABLE command. If not. I want to use the CREATE TABLE command.

 

[Zografski]

TEXT TO lcSql TEXTMERGE NOSHOW PRETEXT 15
IF object_id('MyTable') IS NOT NULL
/* table exists in a SQL Server database */
ALTER TABLE dbo.MyTable ADD newCol nchar(10) NULL
ELSE
CREATE TABLE [dbo].[MyTable](
[Id] [smallint] IDENTITY(1,1) NOT NULL,
[KodName] [nchar](10) NULL,
[newCol] [nchar](10) NULL
) ON [PRIMARY]
ENDTEXT
lnResult = SQLEXEC(m.lnHandle,m.lcSql)
ASSERT .f.
IF lnResult < 0
AERROR(laError)
MESSAGEBOX(laError(2,1))
ENDIF

 

[Mike Lewis]

Wiremesh,

Are you sure you're in the right place? Transact-SQL is the SQL dialect used by Microsoft SQL Server. It has nothing to do with Visual FoxPro.

If you want to use Visual FoxPro to determine if a given table exists on SQL Server, then use the SQLTABLES() function. See the VFP Help for details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[WIREMESH]

Mike,
I am using Transact SQL from a VFP program. The SQLTables command will do what I need.

Thanks