SQL Server judges whether there is summary for database, table, column, view, stored procedure and function
catalogue1 Introduction Overview2 Database related judgments2.1
1 Introduction Overview
When writing stored procedures with relatively complex business logic, temporary tables or data tables are often used as temporary results storage. But every time I make a judgment on the existence of a table, I often cannot recall the complete SQL writing method. Therefore, recording some commonly used methods for determining the existence of database objects can achieve the goal of rapid search. Exactly: A good memory is better than a bad pen.
2 Database related judgments
2.1. Determine whether the database exists
IFEXISTS (SELECT * FROMsys. databasesWHERE='TEST ') PRINT' Database test exists' ELSEPRINT 'Database test does not exist'
3 Judgments related to data tables
3.1. Determine whether the data table exists
--Method 1 IFOBJECT_ ID (N '[dbo]. [Product]', N'U ') ISNOTNULLPRINT' Datasheet Product exists' ELSEPINT 'Datasheet Product does not exist' - Method 2 IFEXISTS (SELECT * FROMsysobjectsWHERE=OBJECT_ID (N '[dbo]. [Product]') ANDXTYPE='U ') PRINT' Datasheet Product exists' ELSEPINT 'Datasheet Product does not exist' - Method 3 IFEXISTS (SELECT * FROMsysobjectsWHERE=OBJECT_ID (N '[dbo]. [Product]') ANDOBJECTProperty (ID, N 'IsUserTable') =1) PRINT 'Datasheet Product exists' ELSEPRINT' Datasheet Product does not exist '
3.2. Determine whether the temporary table exists
--Method 1 IFOBJECT_ ID (N'tempdb.. # Product ', N'U') ISNOTNULLPRINT 'Temporary Table # Product Exists' ELSEPINT' Temporary Table # Product Does Not Exist '-- Method 2 IFEXISTS (SELECT * FROMtempdb. dbo. sysobjectsWHERE=OBJECT_ID (N'tempdb.. # Product') ANDXTYPE='U ') PRINT' Temporary Table # Product Exists' ELSEPINT 'Temporary Table # Product Does Not Exist'
3.3. Determine whether a column exists in the table
--Method 1 IFCOL_ LENGTH (N '[dbo]. [Product]', 'PRD_ID') ISNOTNULLPRINT 'Table Product has column PRD_ Column PRD does not exist in ID'ELSEPRINT 'table Product_ ID '- Method 2 IFEXISTS (SELECT * FROMsyscolumnsWHERE ID=OBJECT_ID (N' [dbo]. [Product] ') ANDNAME='PRD_ ID ') PRINT' Table Product has column PRD_ Column PRD does not exist in ID'ELSEPRINT 'table Product_ ID '- Method 3 IFEXISTS (SELECT * FROMsysobjectsAINNERJOINsyscolumnsBONA. ID=B.IDWHERE. XTYPE='U'ANDA. NAME='PRODUCt'ANDB. NAME='PRD_ID') PRINT 'Table Product has column PRD_ Column PRD does not exist in ID'ELSEPRINT 'table Product_ ID'
3.4. Determine whether a column is self adding
IFCOLUMNPROPERTY(OBJECT_ID(N'[dbo].[PRODUCT]'),'PRD_ ID ',' ISIdentity ')=1PRINT' Table Product Column PRD_ ID is the self increasing column 'ELSEPRINT' table Product column PRD_ ID is not a self increasing column '
3.5. Determine whether an index exists in the table
IFEXISTS(SELECT*FROMSYSINDEXESWHEREID=OBJECT_ID(N'[dbo].[PRODUCT]')ANDNAME='PK_ The 'PRODUCTS') PRINT' table has an index PK for 'PRODUCT'_ The PRODUCTS'ELSEPRINT 'table' Product 'does not have an index PK_ PRODUCTS'
4 View related judgments
4.1. Determine whether the view exists
--Method 1 IFOBJECT_ ID (N '[dbo]. [BRC_1001]', 'V') ISNOTNULLPRINT 'view BRC_ 1001 has' ELSEPRINT 'view BRC_ 1001 does not exist '- Method 2 IFEXISTS (SELECT * FROMsysobjectswhereid=OBJECT_ID (N' [dbo]. [BRC-1001] ') and OBJECTProperty (ID, N'IsView')=1) PRINT 'view BRC_ 1001 has' ELSEPRINT 'view BRC_ 1001 does not exist '- Method 3 IFEXISTS (SELECT * FROMINFORMATION_SCHEMA. VIEWSWHERETABLE-NAME=N'BRC_1001') PRINT 'View BRC_ 1001 has' ELSEPRINT 'view BRC_ 1001 does not exist '
5 Judgments related to stored procedures
5.1. Determining whether a stored procedure exists
--Method 1 IFOBJECT_ ID (N '[dbo]. [BRC_BomCost]', 'P') ISNOTNULLPRINT 'stored procedure BRC_ BomCost has' ELSEPRINT 'stored procedure BRC_ BomCost does not exist '-- Method 2 IFEXISTS (SELECT * FROMsysobjectsWHERE ID=OBJECT_ID (N' [dbo]. [BRC_BomCost] ') ANDOBJECTProperty (ID, N' IsProcedure ')=1) PRINT' stored procedure BRC_ BomCost has' ELSEPRINT 'stored procedure BRC_ BomCost does not exist '
6 Function related judgment
6.1. Determine whether a function exists
IFEXISTS (SELECT * FROMsysobjectsWHERE ID=OBJECT_ID (N '[dbo]. [BRC_MLTotal]') ANDXTYPEIN (N'FN ', N'IF', N'TF ')) PRINT' function BRC_ MLTotal has' ELSEPRINT 'function BRC_ MLTotal does not exist '
This is the end of this article about SQL Server's determination of whether a database, table, column, view, stored procedure, and function exist. For more information about SQL Server's determination of view functions, please search the previous articles of Script Home or continue to browse the following related articles. I hope you will support Script Home more in the future!
Tag: SQL nbsp Server judges whether there is summary for
Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.
