Check if table exists in SQL Server
For queries like this it is always best to use an INFORMATION_SCHEMA
view. These views are (mostly) standard across many different databases and rarely change from version to version.
To check if a table exists use:
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END
Check if MySQL table exists without using select from syntax?
If you want to be correct, use INFORMATION_SCHEMA.
SELECT *
FROM information_schema.tables
WHERE table_schema = 'yourdb'
AND table_name = 'testtable'
LIMIT 1;
Alternatively, you can use SHOW TABLES
SHOW TABLES LIKE 'yourtable';
If there is a row in the resultset, table exists.
Fastest way to determine if record exists
SELECT TOP 1 products.id FROM products WHERE products.id = ?;
will outperform all of your suggestions as it will terminate execution after it finds the first record.
How to check if a table exists in a given schema
It depends on what you want to test exactly.
Information schema?
To find "whether the table exists" (no matter who's asking), querying the information schema (information_schema.tables
) is incorrect, strictly speaking, because (per documentation):
Only those tables and views are shown that the current user has access
to (by way of being the owner or having some privilege).
The query provided by @kong can return FALSE
, but the table can still exist. It answers the question:
How to check whether a table (or view) exists, and the current user has access to it?
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND table_name = 'table_name'
);
The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (information_schema.tables
is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actually carry all information.
System catalogs
Your question was:
How to check whether a table exists?
SELECT EXISTS (
SELECT FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
);
Use the system catalogs pg_class
and pg_namespace
directly, which is also considerably faster. However, per documentation on pg_class
:
The catalog
pg_class
catalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
see alsopg_index
), sequences, views, materialized views, composite
types, and TOAST tables;
For this particular question you can also use the system view pg_tables
. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):
SELECT EXISTS (
SELECT FROM pg_tables
WHERE schemaname = 'schema_name'
AND tablename = 'table_name'
);
Identifiers have to be unique among all objects mentioned above. If you want to ask:
How to check whether a name for a table or similar object in a given schema is taken?
SELECT EXISTS (
SELECT FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
);
- Related answer on dba.SE discussing "Information schema vs. system catalogs"
Alternative: cast to regclass
SELECT 'schema_name.table_name'::regclass;
This raises an exception if the (optionally schema-qualified) table (or other object occupying that name) does not exist.
If you do not schema-qualify the table name, a cast to regclass
defaults to the search_path
and returns the OID for the first table found - or an exception if the table is in none of the listed schemas. Note that the system schemas pg_catalog
and pg_temp
(the schema for temporary objects of the current session) are automatically part of the search_path
.
You can use that and catch a possible exception in a function. Example:
- Check if sequence exists in Postgres (plpgsql)
A query like above avoids possible exceptions and is therefore slightly faster.
Note that the each component of the name is treated as identifier here - as opposed to above queries where names are given as literal strings. Identifiers are cast to lower case unless double-quoted. If you have forced otherwise illegal identifiers with double-quotes, those need to be included. Like:
SELECT '"Dumb_SchName"."FoolishTbl"'::regclass;
See:
- Are PostgreSQL column names case-sensitive?
to_regclass(rel_name)
in Postgres 9.4+
Much simpler now:
SELECT to_regclass('schema_name.table_name');
Same as the cast, but it returns ...
... null rather than throwing an error if the name is not found
Check if table exists and if it doesn't exist, create it in SQL Server 2008
Something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[YourTable](
....
....
....
)
END
T-SQL Check if table exists in schema
You could use the schemas
object as well. For example:
IF EXISTS (SELECT 1
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.[name] = N'schema_name'
AND t.name = N'table_name'
AND t.type = 'U')
DROP TABLE [schema_name].[table_name];
GO
Using SQL query to determine if a table exists
You can do this (in oracle, in mssql there is a bit different):
select count(*)
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'your_table_name';
How to check if a column exists in a SQL Server table
SQL Server 2005 onwards:
IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'columnName'
AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
-- Column Exists
END
Martin Smith's version is shorter:
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
-- Column Exists
END
Check and Return rows if SQL Server table exists
You don't receive your result, apparently. That's because you don't insert your dataset in the result table:
insert into @returntable
SELECT university_id,...
Add this and it should work. And change this existence check as well because everyone thinks that the problem is there:)
You should be able to simply use this form
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Feeds]') AND type in (N'U'))
--abc
ELSE
-- xyz
The same with the information_schema:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'feeds')
BEGIN
print 'exists'
END
Check if entry in table A exists in table B
SELECT *
FROM B
WHERE NOT EXISTS (SELECT 1
FROM A
WHERE A.ID = B.ID)
Related Topics
Replacing Only The Nth Instance of Character
Rodbc and Microsoft SQL Server: Truncating Long Character Strings
Select The First Row in a Join of Two Tables in One Statement
Using SQL Query to Find Details of Customers Who Ordered > X Types of Products
What Is The Max Size of Varchar2 in Pl/Sql and Sql
Aspentech Infoplus 21 - How to Connect and Query Data
Exporting Binary File Data (Images) from SQL via a Stored Procedure
SQL Datedifference in a Where Clause
Sql Server Begin/End Vs Begin Trans/Commit/Rollback
How to Convert Integer to Decimal in SQL Server Query
Why Does Connect by Level on a Table Return Extra Rows
Sql Server Store Multiple Values in SQL Variable
Ms Access - Execute a Saved Query by Name in Vba
What Does "Where 1" Mean in Sql
In SQL Server, How to Create While Loop in Select