This allows the sysobjects form to be useful. Although I do not recommend that you update this form, you certainly have the right to review it.
sysobjects table
Each object created within the database (constraints, default values, logs, rules, stored procedures, and so on) occupies a single row in the table. Only within tempdb, each temporary object occupies one row in the table.
sysobjects table structure:
Column Name |
Data type |
Describe |
Name |
sysname |
object names, common columns |
Id |
Int |
Object identification number |
Xtype |
CHAR (2) |
The object type. Common columns. xtype can be one of the following object types: 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 |
Uid |
smallint |
Owner User Object number |
Info |
smallint |
Keep. Internal Use only |
Status |
Int |
Keep. Internal Use only |
Base_schema_ ver |
Int |
Keep. Internal Use only |
Replinfo |
Int |
Keep. For replication use |
Parent_obj |
Int |
The object identification number of the parent object (for example, for a trigger or constraint, which is the table ID). |
Crdate |
Datetime |
The date the object was created. |
Ftcatid |
smallint |
Full-text catalog identifier for all user tables registered for full-text indexing, 0 for all user tables not registered |
Schema_ver |
Int |
The version number, which is incremented each time the schema of the table changes. |
Stats_schema_ ver |
Int |
Keep. Internal use only. |
Type |
CHAR (2) |
The object type. Can be one of the following values: C = CHECK Constraint D = default value or defaults constraint F = FOREIGN KEY constraint FN = Scalar function IF = inline table function K = PRIMARY KEY or UNIQUE constraint L = Log P = stored procedure R = Regular RF = copy filter stored procedure S = system table TF = table function TR = trigger U = User Table V = view X = Extended stored Procedure |
Userstat |
smallint |
Keep. |
Sysstat |
smallint |
Internal status information |
Indexdel |
smallint |
Keep |
Refdate |
Datetime |
Retained |
Version |
Int |
Keep |
Deltrig |
Int |
Keep |
Instrig |
Int |
Keep |
Updtrig |
Int |
Keep |
Seltrig |
Int |
Keep |
Category |
Int |
For publishing, constraining, and identifying |
Cache |
smallint |
Keep |
You can use the following command to list all objects of interest:
SELECT * from sysobjects where xtype = <type of interest>--For example: View View select * from sysobjects WHERE xtype = ' V '
Determine if a table already exists in the database, and then delete the table
--Method One: if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Table name] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) drop table [dbo]. [Table name]--method Two: if exists (SELECT * from sysobjects where id = object_id (n ' table name ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1) Drop Table [dbo]. [Table name]--method Three: if (Exists (Select * from SysObjects Where xtype= ' U ' and name= ' table name ') drop table [dbo]. [Table name]
Continue to add later.
Some questions from some friends:
1. Q: What does n ' mean in object_id (n ' table name ')?
A: N ' represents the Unicode type. Can support object names in different languages
2. Q: SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Usertab] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1
The object_id in this sentence (N ' [dbo].[ Usertab] and OBJECTPROPERTY (ID, N ' isusertable ') = 1
What is the meaning?
Answer: object_id (N ' [dbo].[ Usertab]: Is the unique ID that the system assigns to the table Usertab
OBJECTPROPERTY (ID, N ' isusertable ') = 1
The property of the object is the use of the OBJECTPROPERTY (id,property) function of the table type.
3. Q: Use master SELECT * from ... SysObjects "... SysObjects"--what does the prefix of three periods mean?
A: A Transact-SQL reference to a database object name can be a four-part name in the following format: [server_name.[ [database_name]. [Owner_name]. | database_name. [Owner_name]. | Owner_name.] ] Object_name
To be continued (xx is not successful, the back depends on everyone) ...
SQL Server system table sysobjects introduction and use (EXT))