SQL Server determines whether a database, table, column, or view exists

Original http://www.cnblogs.com/zhenpingwang/archive/2013/04/15/3021979.html

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 'myview52' exists. If exists (select table_name from information_schema.views where table_name = n 'myview52') print ': 'else print' does not exist'6. Determine whether a function exists.-- Determine whether the name of the function to be created existsIf 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, the following c = check constraints are usually included: D = default value or default constraint F = foreign key constraint L = Log fn = scalar function if = embedded table function P = Stored Procedure PK = primary key constraint (K type) rf = copy and 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 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'

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 'myview52' exists. If exists (select table_name from information_schema.views where table_name = n 'myview52') print ': 'else print' does not exist'6. Determine whether a function exists.-- Determine whether the name of the function to be created existsIf 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, the following c = check constraints are usually included: D = default value or default constraint F = foreign key constraint L = Log fn = scalar function if = embedded table function P = Stored Procedure PK = primary key constraint (K type) rf = copy and 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 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'