SQL Server determines whether a database, table, column, and view exists .... (Note: Reprint)

 

1. Determine whether the database exists
If exists (select * From SYS. databases where name = 'database name ')
Drop database [database name]

2. Check whether the table exists.
If exists (select * From sysobjects where id = object_id (n '[Table name]') and objectproperty (ID, N 'isusertable') = 1)
Drop table [Table name]

3. Determine whether a stored procedure exists
If exists (select * From sysobjects where id = object_id (n' [stored procedure name] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [stored procedure name]

4. Determine whether a temporary table exists
If object_id ('tempdb .. # temporary table name') is not null
Drop table # temporary table name

5. Determine whether a view exists 

-- Determine whether the attempt 'myview52' exists
If exists (select table_name from information_schema.views where table_name = n 'myview52 ')
Print 'exist'
Else
Print 'nonexistent'
6. Determine whether a function exists.
-- Determine whether the name of the function to be created exists
 If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [function name] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [function name]

7. obtain information about the object created by the user

Select [name], [ID], crdate from sysobjects where xtype = 'U'

/*
Xtype indicates the parameter type, which usually includes the following
C = check Constraints
D = default value or default Constraint
F = foreign key constraint
L = Log
Fn = scalar function
If = embedded table functions
P = Stored Procedure
PK = primary key constraint (type: K)
Rf = copy and filter the Stored Procedure
S = system table
TF = table functions
Tr = trigger
U = User table
Uq = unique constraint (type is K)
V = View
X = Extended Stored Procedure
*/

8. Determine whether a Column exists
If exists (select * From syscolumns where id = object_id ('table name') and name = 'column name ')
Alter table Table Name drop column name

9. Determine whether the column is an auto-incrementing column.
If columnproperty (object_id ('table'), 'col', 'isidentity ') = 1
Print 'auto-incrementing column'
Else
Print 'not auto-incrementing column'

Select * From SYS. columns where object_id = object_id ('table name') and is_identity = 1

10. Check whether an index exists in the table.

If exists (select * From sysindexes where id = object_id ('table name') and name = 'index name ')
Print 'exist'
Else
Print 'nonexistent'

11. view objects in the database

Select * From SYS. sysobjects where name = 'object name' select * From SYS. sysobjects where name = 'object name'