system tables sysobjects, syscolumns, and function object_id in SQL Server

1, sysobjects

The System Object table. Objects that hold the current database, such as constraints, default values, logs, rules, stored procedures, and so on

sysobjects important field Explanation:

SysObjects (

Name sysname,--object names

ID int,--object ID

Xtype char (2),--Type of object

Type char (2),--Object types (which appear to be identical to xtype)

UID smallint,--ID of object owner

...--the other fields are not commonly used.

)

?

Note: It is necessary to explain that the xtype and type are identical, and his data is:

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

?

?

?

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 ')

?

4. Usage of object_id function in SQL

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 ')