Check whether a table or index exists in SQL Server

Query whether a table exists:In practice, you may need to delete a table. before deleting a table, you are advised to check whether the table exists to prevent returning error messages. In SQL Server, you can use the following statement: If object_id (n'table name', n'u') is not null drop table name; note, differences between common tables and temporary tables: If you want to delete DBO from the test database. testtable, directly add DBO. use testtable as the table name. If you want to delete the temporary table DBO. # temp_table, use tempdb. DBO. # temp_table as the table name.

 

Query whether an index exists in a table:Generally, to improve data query efficiency, we create indexes for specific fields in the table. The index names in the same table are different. How can we determine whether a table has an index with a specific name? The following statement can be used to determine: select 1 from sys. indexes where object_id = object_id (@ tname, N 'U') and name = @ INAME where: @ tname indicates the name of the table to be indexed, and @ INAME indicates the index name.

 

Extended knowledgeIn each database, SYS. sysobjects is used to include every object (such as constraints, default values, logs, rules, and stored procedures) created in the database ). For more information, see the help document on msdn: SYS. sysobjectsobject_id is used to return the database object ID of objects within the framework. If the database or object name cannot be found, for example, the corresponding name does not exist or the spelling is incorrect, null is returned. For more information, see the help document on msdn: object_idsys.indexes is used to save the index or heap of each table object (such as a table, view, or table value function, for more information, see the help documentation on msdn: SYS. indexes