Original: SQL Server system table sysobjects introduction and use
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.
If you see this text, you are using RSS to read or turn to "a Tree-blog park," The original address: http://www.cnblogs.com/atree/p/SQL-Server-sysobjects.html
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 |
reserved |
Deltrig |
Int |
reserved |
Instrig |
int |
reserved |
Updtrig |
int |
reserved |
Seltrig |
int |
reserved |
Category |
int |
For publishing, constraining, and identifying |
Cache |
smallint |
reserved |
You can use the following command to list all objects of interest:
SELECT * from WHERE = < of Interest>-- For example: View view SELECT* from WHERE='V'
Determine if a table already exists in the database, and then delete the table
--method One:if exists(Select * fromDbo.sysobjectswhereId= object_id(N'[dbo]. [Table name]') and ObjectProperty(ID, N'isusertable')= 1)Drop Table [dbo].[Table name]--Method Two:if exists(Select * fromsysobjectswhereId= object_id(N'Table name') and ObjectProperty(ID, N'isusertable')= 1)Drop Table [dbo].[Table name]--Method Three:if(Exists(Select * fromSysObjectsWhereXtype='U' andName='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 ')?
Answer: 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
To be continued (xx is not successful, the back depends on everyone) ...
SQL Server System table sysobjects describes and uses