1, sysobjects
The System Object table. Objects that hold the current database, such as constraints, default values, logs, rules, stored procedures, and so on
In most cases, the two columns that are most useful to you are sysobjects.name and Sysobjects.xtype. The previous one lists the names of the objects to be examined, and the latter defines the type of the object.
Column Name |
Data Type |
Description |
Name |
sysname |
The name of the object. |
Id |
Int |
The object identification number. |
Xtype |
CHAR (2) |
The object type. Can be one of the following object types: C = CHECK Constraint D = defaults or DEFAULT constraints 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 is K) V = view X = Extended Stored Procedure |
Uid |
smallint |
The user ID of the owner object. |
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, or 0 for all user tables that are 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 = defaults or DEFAULT constraints F = FOREIGN KEY constraint FN = Scalar function IF = Inline Table function K = PRIMARY KEY or UNIQUE constraint L = Log P = Stored Procedure R = Rule 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 state information. |
Indexdel |
smallint |
Keep. |
Refdate |
Datetime |
reserved for later use. |
Version |
Int |
reserved for later use. |
Deltrig |
Int |
Keep. |
Instrig |
Int |
Keep. |
Updtrig |
Int |
Keep. |
Seltrig |
Int |
Keep. |
Category |
Int |
Used for publishing, constraints, and identification. |
Cache |
smallint |
Keep. |
2, Sysolumns
All fields in the current database are kept inside.
Important Field Explanations:
Syscolumns (
Name sysname,--field name
ID int,--The ID of the table to which the field belongs
Xtype TinyInt,--the field type, associated systypes table
Length smallint,--the field is physically stored
...
)
3. sysobjects and syscolumns in SQL
View all table names:
Select name from sysobjects where type= ' U '
All field names for the query table:
Select name from syscolumns Where id=object_id (' Table name ')
int object_id (' objectname ');
This method returns the database object identification number.
Where the parameter objectname represents the object to be used, whose data type is nchar or char (if char, the system converts it to nchar)
The return type is int, which represents the number of the object in the system.
Like what:
Use Wf_timesheet
Select object_id (' usp_check_excess_hours ')
SELECT * FROM dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Company_rel] ')
(N ' ' represents the Unicode type. Can support different language object names)
The SQL SERVER database system table Systypes stores the field types
Sysobjects, sysolumns, systypes
in SQL Server