TableExists
Updated: 30 October 2008
Use wct.TableExists to determine if the specified table exists in the database.
Syntax
SELECT [westclintech].[wct].[TableExists](
<@Schema, nvarchar(4000),>
<@TableName, nvarchar(4000),>)
Arguments
@Schema
is the name of schema containing the table to test for. @Schema is an expression of the character string data type category.
@TableName
is the name of table being tested for on the database. @TableName is an expression of the character string data type category.
Return Types
bit
Remarks
· TableExists simplifies SQL intended to drop a table if it exists.
Example
First create a test table
CREATE TABLE [dbo].[MyTable](
[Test1] [nchar](10) COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY]
The TableExists function returns a SQL Server bit value indicating existence of the table.
Select wct.TableExists('dbo','MyTable')
Here is the result set
-----
1
Use the TableExists function in scripts to drop the table before creating it. This is much easier than querying system tables, which is the commonly used method.
IF (wct.TableExists('dbo','myTable') <> 0) DROP TABLE dbo.myTable
Here is the result
-----
Command(s) completed successfully.
Select wct.TableExists('dbo','MyTable')
Here is the result set
-----
0