T-SQL----system tables

T-SQL

T-SQL----system tables

T-SQL-----stored Procedures

Objective

Although the orm,nosql to the data now reduces the number of T-SQL programming, but T-SQL this should be the master of every programmer, the next two weeks are ready to put this knowledge point system summary.

1. System tables

Stores all system information for SQL. Stores metadata about the database service so-called metadata (such as which user databases the data server has, which login accounts the database server has),

What tables are in the database, which fields each database has stored procedures, views, and so on, and the system table is usually the beginning of sys.

Any user should not directly change the system tables. ( do not attempt to modify system tables using DELETE, UPDATE, INSERT statements, or user-defined triggers.) )

To retrieve the information stored in the system tables, the application should use one of the following components:

• System stored Procedures

• Transact-SQL statements and functions

• SQL Server Management Objects (SMO)

• Replication Management Objects (RMO)

• Database API Directory functions

2. Several important system tables 2.1.sysdatabases

Each database on Microsoft SQL Server occupies a row in the table.

When you initially install SQL Server, sysdatabases contains entries for the master, model, msdb, mssqlweb, and tempdb databases.

Key fields:

Name: The names of the libraries;

Dbid: The id,dbid of the library is system from 1 to 5. These are the five libraries: master, model, msdb, mssqlweb, tempdb.

SELECT * from master.dbo.sysdatabases can query out all the library names.

2.2.Sysobjects

Each database in Sql-server has this system table, which holds all objects created within the database, such as constraints, defaults, logs, rules, stored procedures, and so on.

Key fields
Name: Object names

ID: Object ID

Xtype (Type): Object type

UID: Owner object User ID

Status: Object state.

    Object Type (xtype). It can be one of the following object types:
C = CHECK constraint  
D = default value, or  
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 is K)  
V = view  
X = extended stored procedure  
When Xtype= ' U ' and status>0 represent a table created by the user, the object name is the table name, and the object ID is the ID value of the table (object_id (' table name ')).                        

Select *  from where xtype='U' and Status>0--  You can list the libraries

Determine if there is

if (object_id ('proc_get_student ', ' P ')   is not null)-- determine if the stored procedure has a different if (exists (select  * FROM sys.objects where name = 'proc_get_student'))//    

Note:proc_get_student is a stored procedure name, p represents the object type for the stored procedure, and the object type is listed above.

2.3.syscolumns

Each column in the table and view occupies a row in the table, and each parameter in the stored procedure also occupies a row in the table. The table is located in each database.

Key fields are:

Name: Field names

ID: ID Number of the table

Colid: Field ID number.

The ID is the ID number of the table we got with sysobjects just above.

Determine if there is

     IF col_length('A','C') is  not NULLNote: A is the table name c is the column name or:Select   *    fromsyscolumnswhereId=object_id('Table name') andName='Column Name'Note: A description record of this column is returned, and there is no return null;

3. Other system tables

Wyl_dbwyl_db.dbo. Sysaltfiles saving a database's file syscharsets    character set and sort order sysconfigures   configuration options syscurconfigs   current configuration options syslanguages    language syslogins      login account information sysoledbusers   link Server login information sysprocesses   process sysremotelogins telnet account Sysconstrains  Restrict sysfilegroups filegroup  sysfiles      file sysforeignkeys  external keyword sysindexs     index sysmenbers    role member Syspermissions  Permissions systypes      user-defined data type sysusers      user

T-SQL----system tables