SQL Server sysobjects

All information of the SQL Server database is stored in its system table.

In most cases, the two most useful columns are sysobjects. Name and sysobjects. xtype.

The previous one is used to list the names of objects to be evaluated, and the next one is used to define the object type. Each object created by sysobjects in the database (constraints, default values, logs, rules, stored procedures, etc) occupies one row in the table.

Each temporary object occupies one row in the table only in tempdb.

Column name data type description
Name sysname object name.
Id int ID of the object.
Xtype char (2)
Object type.

1. Object Name

Object Name (name) can be one of the following object types:
C = check Constraints
D = default value or default Constraint
F = foreign key constraint
L = Log
Fn = scalar function
If = embedded table functions
P = Stored Procedure
PK = primary key constraint (type: K)
Rf = copy and filter the Stored Procedure
S = system table
TF = table functions
Tr = trigger
U = User table
Uq = unique constraint (type is K)
V = View
X = Extended Stored Procedure
User ID of the UID smallint owner object.
Info smallint is retained.
For internal use only.
Status int is retained.
For internal use only.
Base_schema _ ver int is retained.
For internal use only.
Replinfo int is retained.
For replication.
The object ID of the parent object of parent_obj int (for example, this ID is the table ID for triggers or constraints ).
The creation date of the crdate datetime object.
Ftcatid smallint is the full-text directory identifier of all user tables registered with the full-text index. It is 0 for all user tables not registered.
Schema_ver int version number, which increases each time the schema of the table is changed.
Stats_schema _ ver int is retained.
For internal use only.

2. Object Type

The object type (xtype) exists in 6.0, and xtype exists in 7.0: Type object type.
It can be one of the following values:
C = check Constraints
D = default value or default Constraint
F = foreign key constraint
Fn = scalar function
If = embedded table functions
K = primary key or unique constraint
L = Log
P = Stored Procedure
R = rule
Rf = copy and filter the Stored Procedure
S = system table
TF = table functions
Tr = trigger
U = User table
V = View
X = Extended Stored Procedure xtype object type.
It can be one of the following object types:
C = check Constraints
D = default value or default Constraint
F = foreign key constraint
L = Log
Fn = scalar function
If = embedded table functions
P = Stored Procedure
PK = primary key constraint (type: K)
Rf = copy and filter the Stored Procedure
S = system table
TF = table functions
Tr = trigger
U = User table
Uq = unique constraint (type is K)
V = View
X = Extended Stored Procedure

3. FAQs

1. Question: object_id (N in n 'indicates what if exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [text] ') and objectproperty (ID, n' isusertable') = 1) Drop table [DBO]. [text]
A: n'' indicates the Unicode type, abbreviated as "national. Object names in different languages are supported.

2. Q: Select * From DBO. sysobjects where id = object_id (n' [DBO]. [usertab] ') and objectproperty (ID, N 'isusertable') = object_id (n' [DBO] in 1. [usertab] ') and objectproperty (ID, n' isusertable') = 1?
A: object_id (n' [DBO]. [usertab] '): obtains the unique ID objectproperty (ID, n'isusertable') assigned to the table usertab by the system. = 1. the attribute of this object is the objectproperty (ID, property) function usage,
Reference: http://www.cnblogs.com/greateast/archive/2007/07/31/837312.html

3. Q: What is the prefix of the three periods "use master select * from... sysobjects?
A: The Transact-SQL reference to the database object name can be composed of four parts. The format is as follows: [SERVER_NAME. [[database_name]. [owner_name]. | database_name. [owner_name]. | owner_name.] object_name When referencing a specific object, you do not have to always specify the server, database, and owner that identifies the object for SQL Server. You can omit intermediate nodes and use periods to represent these locations. The valid format of the Object Name is server. database. Owner. object.
Server. database.. Object Server... owner. Object Server... object database. Owner. Object Database... object owner. Object owner. Object
Example:
1. find all user tables: Select ID, name, xtype, crdate from sysobjects where xtype = 'U' order by name; or select ID, name, xtype, crdate from sysobjects where objectproperty (ID, N 'isusertable') = 1 order by name;
2. find all stored procedures select ID, name, xtype, crdate from sysobjects where xtype = 'P' order by name; or select ID, name, xtype, crdate from sysobjects where objectproperty (ID, N 'isprocedure ') = 1 order by name;