SQL Server System table sysobjects describes and uses

　　　　　　　　　　　　　　　　　　　　

All information about the SQL Server database is stored in its system table. I wonder if you have spent more time checking the system tables because you are always busy with user forms. However, you may need to do something unusual occasionally, such as all the triggers in the database. You can check the table one by one, but if you have 500 tables, this can be quite labor-intensive.

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

Turn from--a tree

SQL Server System table sysobjects describes and uses