SQL Server system table sysobjects introduction and use (GO)

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. The 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 = Save Stored process 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. It 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 sysobjectsWHERE xtype=<typeOf interest>--Example: View viewSELECT* from sysobjects where xtype =  ' v '       

Determine if a table already exists in the database, and then delete the table

--Method One:IfExists (Select*From dbo.sysobjectswhere ID=OBJECT_ID (N‘[dbo]. [Table name]‘)andOBJECTPROPERTY (ID, N‘Isusertable‘)=1)DropTable[Dbo].[Table name]--Method Two:IfExists (Select*From sysobjectswhere ID=OBJECT_ID (N‘Table name‘)andOBJECTPROPERTY (ID, N‘Isusertable‘)=1)DropTable[Dbo].[Table name]--Method Three:IfExists (Select*From SysObjectsWhere xtype=‘U‘and Name=‘Table name‘))Droptable [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 in this sentence object_id (n ' [dbo].[ Usertab] and OBJECTPROPERTY (ID, N ' isusertable ') = 1 What does it mean?

Answer: object_id (N ' [dbo].[ Usertab]: Is the unique ID assigned by the system to the table Usertab objectproperty (ID, N ' isusertable ') = 1 The object's property is a table-type OBJECTPROPERTY (id,property ) function is used,

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 (GO)