Check whether a table or index exists in SQL Server

Source: Internet
Author: User
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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.