SQL Server System Table

1. sysobject:
Store all objects in the database!
Heaviest Field
1.1 name, ID, crdate, xtype (U: User table, V: View, TR: trigger, P: stored procedure, S, system table)

1.2 common functions: object_id ('object name') and object_name
In addition, if we want to generate an equal-difference series like 1, 2, 3, 4, and 5, we can do this.
Select Identity (INT, 1, 1) as ID into # From sysobjects, syscolumns
2. syscolumns
Stores information about each column.
Heaviest Field
2.1 name, ID, colid -- the order in which fields are stored in the table
If there are many fields in a table and you want to list all the fields except a field in a table, you can write
Declare @ fields varchar (5000)
Select @ fields = @ fields + name
From syscolumns
Where id = object_id ('test') and name not in ('field 1', 'field 2 ')
Another example is how to use column numbers to select specific columns.
Create Function f_columnorder (@ tablename varchar (10), @ colid INT)
Returns table
As
Return
Select name from syscolumns
Where id = object_id (@ tablename) and colid = @ colid
-- Try
Select * From DBO. f_columnorder ('users', 2)

3. sysfiles
If you want to know the SQL data file storage directory, you can use
Select filename from sysfiles
4. syscomments
Statement used to save a view or stored procedure!
5. sysforeignkeys
Fkeyid: foreign key table Object ID, rkeyid: primary key table Object ID
Sometimes we want to remove all tables in the database, but there are constraints. Some tables must be deleted before the primary table; otherwise, an error is reported,

In this case, we use this table to implement
**************************************** ******************
System table in master table
Sysdatabases
Important Fields
1.1 name, dbid, crdate
Common Function db_id ('database name ')
Sysaltfiles
Save the physical address of the MDF file of each database
Sysprocesses
Save Process Information
Kill spid to kill a process

5. Note the sp_msforeachtable function
delete a table in a database
exec sp_msforeachtable 'truncate table? '
Note: An error may occur if the table cannot be deleted due to constraints.
6. What should I do if I want to change the system table?
two methods
1. use the query analyzer to modify
use the following statement:
exec sp_configure 'Allow updates ', 1 -- allow modification
reconfigure with override
exec sp_configure 'Allow updates', 0 -- do not allow modification
reconfigure with override
2. change through Enterprise Manager
right-click an SQL instance and choose "properties"> "allow" to modify the system directory.