SQL Server determines whether a table, view, or stored procedure exists

-- It can be used for real tables.
If exists (select * From sysobjects where id = object_id (n' [DBO]. [Table name] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [Table name]

-- It can be used for temporary tables (Note: if you use the real-table lookup method to create a temporary table, the difference between. Release and generation is not found .)
If object_id ('tempdb .. # temp ') is not null
Drop table # temp

-- Determine whether a stored procedure exists
Mssql2000

 [C-sharp]View plaincopyprint?

1. If exists (select * From sysobjects where id = object_id ('[DBO]. [sp_create_storecount]') and xtype = 'P ')
2. Drop procedure [DBO]. [sp_create_storecount]

Mssql2005

[XHTML]View plaincopyprint?

1. If exists (select * From SYS. objects where object_id = object_id (n' [DBO]. [accounthz] ') and type in (n'p', n'pc '))
2. Drop procedure [DBO]. [accounthz]

-- Determine whether a view exists
-- SQL Server 2000
If exists (select * From sysviews where object_id = '[DBO]. [view name]'
-- SQL Server 2005
If exists (select * From SYS. Views where object_id = '[DBO]. [view name]'

/*
Sysobjects (
Name sysname, -- Object Name
Id int, -- Object ID
Xtype char (2), -- Object Type
Type char (2), -- object type (exactly the same as xtype? A little depressing ...)
UID smallint, -- ID of the object owner
... -- Other fields are not commonly used.
)

The xtype of sysobjects indicates the object type. It can be one of the following object types:
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

 

Object_id and data_object_id indicate the unique identifier of the database object.

Object_id is the logical ID of the database object, and data_object_id is the physical ID of the database object.

If some objects do not have physical properties, they do not have data_object_id, such as procedure, function, package, data type, DB link, MV definition, view definition, and temporary table, partition Table definitions and so on. These objects do not correspond to a specific segment, so their data_object_id is empty.

When a table is created, its object_id and data_object_id are equal. After the tables move and truncate, The data_object_id changes. Modifying the table structure does not change.

Select object_id, data_object_id from user_objects where object_name = 'T ';
Object_id data_object_id
---------
63053 63464

Select header_file, header_block, blocks from dba_segments where segment_name = 'T' and owner = 'test ';
Header_file header_block Blocks
------------
4 467 8

*/

Select * From sysobjects where xtype = 'U' and ID = object_id ('booking ')