6 Ways to Check if a Table Exists in SQL Server (T-SQL Examples)
This article offers five options for checking if a table exists in SQL Server. Most options involve querying a system view, but one of the options executes a system stored procedure, and another involves a function.
I also include some simple IF statements that can be modified to suit your circumstance.
Option 1 – The sys.tables View
This option queries the sys.tables system catalog view. This view returns a row for each user table. Therefore, you can query it using the table name you’re checking for.
You can also add the schema name to the things you’re checking for. Here’s how to modify the previous query to include the schema name:
Note that the sys.tables view only returns the schema ID so I had to pass that to the SCHEMA_NAME() function in order to get its name. Alternatively I could have used the schema ID if I’d known that.
Option 2 – The sp_tables Stored Procedure
The next option executes the sp_tables stored procedure.
Here’s how succinct your code can be when using this method:
However, this stored procedure returns views as well as tables, so it’s a good idea to narrow it down to just tables (unless you’re also interested in having views returned). To narrow it down to just tables, use @table_type = «‘TABLE'» .
While you’re at it, you can specify the table owner and table qualifier too.
It’s important to note that the @table_type parameter accepts a comma separated list. Therefore, it’s a bit different to the other parameters. The @table_type value must be enclosed in double quotes, and each item enclosed in single quotes. In my example, there’s only one list item, however, it still needs to be enclosed in both double, and single quotes.
Option 3 – INFORMATION_SCHEMA.TABLES
The INFORMATION_SCHEMA.TABLES system view returns one row for each table or view in the current database for which the current user has permissions. It’s similar to sys.tables , but it returns less columns. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.
Here’s an example of using it to check if a table exists in the current database:
Here it is again, but this time I also specify the schema:
Option 4 – The OBJECT_ID() Function
You can also use a function such as OBJECT_ID() to see if it returns a non-NULL value.
In this case, the table exists. Note that I used U to indicate the object type (user-defined table).
You can also provide a three part name to include the database and schema:
If the table doesn’t exist, you’ll get NULL :
See below for an example of using this in an IF statement.
Option 5 – The sys.objects View
As if none of the previous examples will do the job, here’s yet another way to check if a table exists.
This time I query the sys.objects system catalog view. This view returns a row for each user-defined, schema-scoped object in the database. It doesn’t just return tables, it returns all sorts of objects. Therefore we need to narrow it down to just tables. In this case I’m only interested in user-defined tables, so I can use type = ‘U’ ( U is for “USER_TABLE”). Alternatively, you could use TYPE_DESC = ‘USER_TABLE’ .
Here it is again, but specifying the schema:
Option 6 – The sys.sysobjects View (AVOID)
This option is only listed so that I can recommend against using it. The sys.sysobjects view is included in SQL Server for backwards compatibility, and Microsoft recommends that you avoid using this view in future work.
If you do encounter code that uses this view, consider modifying it to use sys.objects or some other system view or stored procedure.
In any case, here’s what the previous example might look like if using sys.sysobjects instead of sys.objects .
IF Statement 1
Here’s a simple IF statement that checks for the existence of the table, then prints a different message depending on the outcome. This code can be modified to suit your specific needs.
And here’s what it looks like when the table doesn’t exist:
IF Statement 2
Here’s another IF statement that can be modified to suit your specific needs.
Проверьте, существует ли таблица в SQL Server
Я хотел бы, чтобы это была окончательная дискуссия о том, как проверить, существует ли таблица в SQL Server 2000/2005 с помощью инструкции SQL.
когда вы Google для ответа, вы получите столько разных ответов. Есть ли официальный / назад и вперед совместимый способ сделать это?
здесь два возможных способа сделать это. Какой из них является стандартным / лучшим способом сделать это?
MySQL обеспечивает простой
заявление. Я ищу что-то подобное.
22 ответов:
для таких запросов всегда лучше использовать INFORMATION_SCHEMA вид. Эти представления (в основном) являются стандартными для многих различных баз данных и редко меняются от версии к версии.
чтобы проверить, существует ли таблица, используйте:
также обратите внимание, что если по какой-либо причине вам нужно проверить временную таблицу, вы можете сделать это:
мы всегда использовать OBJECT_ID стиль до тех пор, как я помню
Как проверить существует ли таблица sql
В репле psql для просмотра списка таблиц используется команда \d .
Если же репл нам не подходит, можно использовать запрос. В большинстве рекомендаций говорится о том, что стоит делать запрос к самой схеме information_schema.tables . Но такой подход, в данном контексте, неверен, так как выборка покажет только те таблицы, к которым у текущего пользователя есть доступ. Таким образом таблица может существовать, но запрос к information_schema.tables вернёт false .
Рекомендую, при проверке существования таблиц, обращаться напрямую к каталогам pg_class и pg_namespace. Запрос может выглядеть следующим образом:
How to check if a Table exists in Sql Server
Many a times we come across a scenario where we need to execute some code based on whether a Table exists or not. There are different ways of identifying the Table existence in Sql Server, in this article will list out the different approaches which are commonly used and it’s pros and cons. I prefer using the OBJECT_ID() function as it is easy to remember. Let me know which approach you use and reason for the same.
To demo these different approaches let us create a sample database with a table by the below script:
Approach 1: Using INFORMATION_SCHEMA.TABLES view
We can write a query like below to check if a Customers Table exists in the current database.
RESULT:
The above query checks the existence of the Customers table across all the schemas in the current database. Instead of this if you want to check the existence of the Table in a specified Schema and the Specified Database then we can write the above query as below:
RESULT:
Pros of this Approach: INFORMATION_SCHEMA views are portable across different RDBMS systems, so porting to different RDBMS doesn’t require any change.
Approach 2: Using OBJECT_ID() function
We can use OBJECT_ID() function like below to check if a Customers Table exists in the current database.
RESULT:
Specifying the Database Name and Schema Name parts for the Table Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the table in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that even though the current database is MASTER database, we can check the existence of the Customers table in the dbo schema in the SqlHintsDemoDB database.
RESULT:
Pros: Easy to remember. One other notable point to mention about OBJECT_ID() function is: it provides an option to check the existence of the Temporary Table which is created in the current connection context. All other Approaches checks the existence of the Temporary Table created across all the connections context instead of just the current connection context. Below query shows how to check the existence of a Temporary Table using OBJECT_ID() function:
RESULT:
Approach 3: Using sys.Objects Catalog View
We can use the Sys.Objects catalog view to check the existence of the Table as shown below:
RESULT
Approach 4: Using sys.Tables Catalog View
We can use the Sys.Tables catalog view to check the existence of the Table as shown below:
RESULT
Sys.Tables catalog view inherits the rows from the Sys.Objects catalog view, Sys.objects catalog view is referred to as base view where as sys.Tables is referred to as derived view. Sys.Tables will return the rows only for the Table objects whereas Sys.Object view apart from returning the rows for table objects, it returns rows for the objects like: stored procedure, views etc.
Approach 5: Avoid Using sys.sysobjects System table
We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.tables instead of sys.sysobjects system table directly.
RESULT: