Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about the instructions and usage of the Sql Server system tables, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

We usually write general SQL statements, may not use the system table, but in some special cases will be used, for example, in the use of the system, we dynamically created according to the date of the data table, if you want to get data from it, it is best to add to determine whether the table exists, if there is, we will take data from the table, if it does not exist, then jump out.

Let's first introduce the function of each system table, and then take a few examples to illustrate the usage.

SQL system table

The sysaltfiles master database holds the files of the database

Character set and sort order of syscharsets main database

Sysconfigures master database configuration options

Current configuration options for the syscurconfigs primary database

Databases in the sysdatabases master database server

Syslanguages master database language

Login account information of syslogins main database

Sysoledbusers main database link server login information

Sysprocesses master database process

Remote login account for sysremotelogins master database

Syscolumns each database column

Sysconstrains per database limit

Sysfilegroups each database filegroup

Sysfiles each database file

Sysforeignkeys each database external keyword

Sysindexs each database index

Sysmenbers each member of the database role

Sysobjects all database objects per database

Syspermissions permissions per database

Systypes per database user-defined data type

Sysusers every database user

Code demonstration

1. Judge the existence of the data table

We need to query the payment pipelining information from the database. The payment pipelining table defaults to the txsPayFlow table, and the following tXsPayFlow$201805 represents the payment pipelining in May 2018. This table is created automatically every month, so if we want to check the sales in May 2018, we have to query it from the partition table of the generation year.

Normally, let's check the running water in May 18 and write it directly.

Because we do not have the monthly end of June in our software, the table of June is not created. If we want to check the data of June here, we will make a mistake directly by using the above method.

At this time, our system table plays a role, it says that sysobjects can see the objects of the database, so we first judge whether the table exists in the system, if there is fetch data, there is no output without this table information.

So that the code can work properly.

two。 Add fields to the data table

Generally speaking, when our program is upgraded, the database may also add fields. In order not to report errors many times, we need to determine whether there is this field in the table when we add the field.

Judge whether the sysobjects,syscolumns is used in the table where we want to add the field. Through the ID association in the two tables, we can determine whether there are any columns that we want to add.

Example: we want to add a sflag column to the balance table.

Through the associated query, we can see the columns in the system table.

So we write the code to add a column of sflag

When the first execution is finished, the following prompt command has been completed successfully, and we have seen that the columns have been added from the database.

And then we'll do it again.

The following hint tells us that the column already exists.

After reading the above, do you have any further understanding of the instructions and usage of the Sql Server system tables? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.