Sysobjects. xtype in SQL Server

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. Description name sysname Object Name of the Data Type of the column name. Id int ID of the object. Xtype char (2) object type. It can be one of the following object types: C = check constraint d = default value or default constraint F = foreign key constraint L = Log fn = scalar function if = nested table function P = Stored Procedure PK = primary key constraint (type: K) rf = copy and filter Stored Procedure S = system table TF = table function TR = trigger u = User table uq = unique constraint (type: K) V = view X = user ID of the UID smallint owner object of the extended stored procedure. 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. Type char (2) object type. It can be one of the following values: C = check constraint d = default value or default constraint F = foreign key constraint fn = scalar function if = nested table function K = primary key or unique constraint L = log P = Stored Procedure r = rule Rf = copy and filter Stored Procedure S = system table TF = table function TR = trigger u = User table V = view X = Extended Stored Procedure userstat smallint retained. Sysstat smallint internal status information. Indexdel smallint is retained. Refdate datetime is reserved for future use. Version int is reserved for later use. Deltrig int is retained. Instrig int is retained. Updtrig int is retained. Seltrig int is retained. CATEGORY int is used for publishing, constraints, and identifiers. Cache smallint is retained. Type exists in 6.0, and xtype exists in 7.0: Type object type. It can be one of the following values: C = check constraint d = default value or default constraint F = foreign key constraint fn = scalar function if = nested table function K = primary key or unique constraint L = log P = Stored Procedure r = rule Rf = copy and filter Stored Procedure S = system table TF = table function 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 constraint d = default value or default constraint F = foreign key constraint L = Log fn = scalar function if = nested table function P = Stored Procedure PK = primary key constraint (type: K) rf = copy and 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 1. Q: 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] Answer: n' stands for u Nicode type, abbreviated as "national. Supports object names in different languages. 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 use, refer to: http://www.cnblogs.com/greateast/archive/2007/07/31/837312.html 3, Q: use master select * from... sysobjects "... what is the prefix of three periods in 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 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;

Select count (*) from sysobjects where (xtype = 'U') -- queries the total number of tables in a database