1, SYSOBJECTS
System object table. Save the object of the current database, such as constraints, defaults, logs, rules, stored procedures, etc.
Sysobjects Important Field Explanation:
Sysobjects (Name Sysname, - Object Name ID INT, - Object ID Xtype Char (2), - Object Type Type Char (2), - Object Type (Is it a bit of XTYPE? It is a bit depressed ...) Uid Smallint, - Object owner's ID ... - Other fields are not commonly used.)
Among them, it is necessary to explain that Xtype and Type are exactly the same (I don't know what MS is made, and his data is:
C = Check constraint D = default or default constraint f = foreign key constraint fn = scalar function if = embedded table function k = primary key or unique constraint L = log P = stored procedure R = rule RF = copy filtering process S = System Table TF = Table Function TR = Trigger U = User Table V = View X = Extended Storage Process
When will we use SysObjects? most common:
a, I intend to create a table table1 in the database DB1. If Table1 does not exist, it is created directly. If table1 already exists, delete re-establishment.
So, write this in the T-SQL batch command:
Use DB1 Go
IF exists (select * from sysobjects where [name] = 'table1 and xtype =' u ') DROP TABLE TABLE1 Go
Create Table1 (FD1 INT, FD2 INT, ...) GO
-------------------------------------------------- -------------------- The above code is not tested, and write it hand. I hope I will not be wrong :) - 3 leg chairs
Wonderful world, all in the developer club (www.dev-club.com)! ! -------------------------------------------------- ---------------------
b, I plan to count about statistics, how many TBs in a database system, how many sp, how many fn, and how many VWs, how much work is required in rough estimation. Use the system to bring the database Northwind as an example of use northwindgo
Select Isnull (CAST (Xtype As Varchar (4)), 'Total') AS 'Classification', count (*) AS 'count' from sysobjects where xtype = 'u' or xtype = 'p' or xtype = 'fn' Or Xtype = 'V' group by xtype with cube
The result is: Category Count ---- ----------- P 38U 14V 18 Total 70
(The number of rows affects is 5 lines)
-------------------------------------------------- ------------------- Ask, the Northwind database has 0 FN, 38 SP, 14 TB, and 18 VW, with a total of 70 important objects.
The above code has been tested, not written in hand. Certainly not wrong :)
- 3 leg chairs
Wonderful world, all in the developer club (www.dev-club.com)! ! -------------------------------------------------- ---------------------
2, Syscolumns
Database field table. All fields of the current database remain inside.
Important field explanation:
Syscolumns (Name Sysname, - Field Name ID INT, "The id xtype tinyint of the table belonging to this field, - this field type, associated with the Systypes table Length Smallint, - this field physical storage length ...)
The most common application, I want to see a table, which fields, and the length of the field.
For example, I want to see the fields and lengths of the Employees table in Northwind:
Use northwind
Select Object_name (C.ID) AS 'Tablename', C. [Name] as 'Column', T. [Name] AS 'TYPE', C. [Length] from syscolumns c inner Join Systypes T on C.XTYPE = T .XTYPE AND T. [name] <> 'sysname' where c.id = Object_id ('Employees') Order by C.colorder
Then the result is:
Tablename Column Type Length ---------------------------------------------- ----------------- Employees EmployeeID int 4Employees LastName nvarchar 40Employees FirstName nvarchar 20Employees Title nvarchar 60Employees TitleOfCourtesy nvarchar 50Employees BirthDate datetime 8Employees HireDate datetime 8Employees Address nvarchar 120Employees City nvarchar 30Employees Region nvarchar 30Employees PostalCode nvarchar 20Employees Country nvarchar 30Employees HomePhone nvarchar 48Employees Extension nvarchar 8Employees Photo image 16Employees Notes ntext 16Employees ReportsTo int 4Employees PhotoPath nvarchar 510 ---------------------------- ------------------------------------------ Oh, in fact, look at it, you can Seeing the length of Length and the length of the table created is inconsistent (double) ... Note that I have the indication, it is a physical storage length. In fact, the string type is required to divide 2.
Among them, another system table System table is also referred to, briefly illustrates: This system table is used to store data types. I am here for convenience, translating types
. Otherwise, Type is some ID number. It is unhappy.
The above code has been tested, not written in hand. Certainly not wrong :)
- 3 leg chairs
Wonderful world, all in the developer club (www.dev-club.com)! ! -------------------------------------------------- ---------------------
3, SYSUSERS
Current database system groups, and users.
Sysusers (Uid Smallint, - User ID Name Smallint, - Name Uid Varbinary (85), - belongs to a landing ....)
This system table does not say, I will talk about it, I will not be asked to see online help.
Simply put an example: View the current database, there are those users:
Use northwindgo
Select * from sysusers where status <> 0
The result is:
Uid ID Name SID Other Fields () --------------------------------------------------------------------------------------------------- -------------------------------------- 2 2 Guest 0x001 2 dbo 0x015 2 chair3 0x8750E1247f2bdf4280c44d23fdadeaf6
4, sysdenpends
The dependency of the current database. how to say? I didn't know how to express it.
Just make an example. For example, I modified someone else's garbage code, it can be serious and uncomfortable, then you need to modify his table, then I am worried that I will affect other SP, VW, or Fn. At this time, I need to check it first and see if those programs are called.
The following example shows that from the Northwind database, who quoted products
then:
Use northwind
Select Distinct Object_name (D.ID) AS 'Program', O.XType from Sysdepends D Inner Join Sysobjects O on D.Id = O.ID where object_name (depid) = 'Products'
So the result
Program xtype ------------------------------------ Alphabetical List of Products V CK_Products_Unitprice C CK_REORDERLEVEL C CK_UNITSINSTOCK C CK_UNITSONORDER C Current Product List V CustOrderHist P CustOrdersDetail P Invoices V Order Details Extended V Product Sales for 1997 V Products Above Average Price V Products by Category V Sales by Category V SalesByCategory P Ten Most Expensive Products P
-------------------------------------------------- -------------------- Look, let's understand? As for the type of XType, it has already been introduced in sysobjects.
The above code has been tested, not written in hand. Certainly not wrong :)
- 3 leg chairs
Wonderful world, all in the developer club (www.dev-club.com)! ! -------------------------------------------------- ---------------------
All right. The system table for general programmers is basically these few.
Other special system tables (mainly in Master or Tempdb), everyone can explore themselves. Or later it later. :)
