SQL SERVER Determines if there is a database, table, column, view
--1. Determine if the database existsIF EXISTS(SELECT * fromSYS. DATABASESWHERENAME= 'Database name') DROP DATABASE [Database name] --2. Determine if a table existsIF EXISTS(SELECT * fromSYSOBJECTSWHEREId= object_id(N'[table name]') and ObjectProperty(ID, N'isusertable')= 1) PRINT 'exist' ELSE PRINT 'does not exist'--3. Determine if the stored procedure existsIF EXISTS(SELECT * fromSYSOBJECTSWHEREId= object_id(N'[Stored procedure name]') and ObjectProperty(ID, N'isprocedure')= 1) PRINT 'exist' ELSE PRINT 'does not exist'--4. Determine if a temporary table existsIF object_id('TEMPDB: #临时表名') is not NULL PRINT 'exist' ELSE PRINT 'does not exist'--5. Determine if the view exists--SQL SERVER 2005IF EXISTS(SELECT * fromSYS. ViewsWHERE object_id = '[DBO]. [View name]' PRINT 'exist' ELSE PRINT 'does not exist'--6. Determine if a function existsIF EXISTS(SELECT * fromDbo. SYSOBJECTSWHEREId= object_id(N'[DBO]. [function name]') andXTYPEinch(N'FN'N'IF'N'TF')) PRINT 'exist' ELSE PRINT 'does not exist'--7. Get user-created object informationSELECT [NAME],[ID], Crdate fromSYSOBJECTSWHEREXTYPE='U' /*the XTYPE represents the parameter type, usually including the following C = CHECK constraint D = default value or defaults constraint F = FOREIGN KEY constraint L = log FN = scalar function IF = inline table function P = stored procedure PK = PRIMARY KEY constraint (type is k) RF = copy Filter stored procedure S = system table TF = table function TR = trigger U = User Table UQ = UNIQUE constraint (type k) V = view X = Extended stored Procedure*/ --8. Determine if a column existsIF EXISTS(SELECT * fromsyscolumnsWHEREId= object_id('Table name') andNAME= 'Column Name') PRINT 'exist' ELSE PRINT 'does not exist'--9. Determine if the column is self-incrementIF ColumnProperty(object_id('Table name'),'Column Name','isidentity')= 1 PRINT 'self-increment column' ELSE PRINT 'not self-adding columns' SELECT * fromSYS. COLUMNSWHERE object_id = object_id('Table name') andIs_identity= 1--10. Determine if an index exists in the tableIF EXISTS(SELECT * fromsysindexesWHEREId= object_id('Table name') andNAME='Index name') PRINT 'exist' ELSE PRINT 'does not exist'--11. Viewing objects in the databaseSELECT * fromSYS. SYSOBJECTSWHERENAME='Object Name'
SQL SERVER Determines if there is a database, table, column, view