The System View in SQL Server is described in detail.

The System View in SQL Server is described in detail.

I wanted to write something better about this series. Later I thought that as a programmer, the most common application is database, but in fact many coders like me work on the front-line, I am familiar with SQL, so do not talk about optimization or understanding the underlying database...

I. System views

1. What is the system view?

From the perspective of name, do you know the system view? A good guess is to store some information about the SQL server system. It's good. Congratulations, you are correct.

2. Where are all definitions?

In order to let you see the truth, let's take a look at it. From this, you can see that both "System Database" and "User Database" have these system views, and even the names are the same at a glance.

3. What benefits can these system views bring to me?

Q1: When I maintain a system, I only know that one database has a table field named "state", but I forgot to define it in that table? How can I find it?

A1: This is simple. A System View named INFORMATION_SCHEMA.COLUMNS is provided in sqlserver. Let's take a look.

We can see from the SCHEMA word in the name of this system view that it was originally a view that saved the table architecture, and there are also the "qualifying" and "default" features of this field, is there a great feeling ???

Q2: I saw a stored procedure named "CategoryInsert" in C # code. I want to see its source code, but there are thousands of stored procedures in my table, I cannot find them one by one. Can you view them quickly in the System View?

A2: so easy... Tell you, what you can't think of is that you can't do without a system view. Isn't it a simple view of Stored Procedure Code? Sys. SQL _modules can be implemented for you.

 

Q3: This method is good, but the definition field of copy is not formatted .... Eldest Brother, thousands of SQL lines... What should I do if I want to format the output? Thank you.

A3: As you said, If you format the output, the system View can only help you here, but there is no such thing as a path. You can use the system stored procedure, which has a magical sp_helptext, thank you for realizing your dream.

　　

Ii. Thoughts on the System View

In the code above, I demonstrated the benefits of two system views and one proc, so take a closer look, you will have two questions ....

1: Where is the System View defined?

This is really a question. From the beginning of this article, we know that my user library MYPETSHOP has many system views, but I really didn't define these views. God can testify to me, the problem is mysterious. Where did system view come from? You only have to ask the sqlserver team this question. They put the system view into a hidden resource database. Where is the database? I will find it for you.

After finding it, I will attach it now. If you are smart enough, you cannot load it directly. Otherwise, it will report that the process is in use. I think you know the reason.

 

The solution is also very simple. We make a copy to the E disk. Then attach this copy.

Now that I have attached it, I feel that I can't wait to take a look at it. I think you should understand something carefully through the following, these views are not in the "System view" folder, but are actually used as user views... Right...

2: Where is the data source of the System view?

This is also a classic problem. Since it is a view, I think everyone understands that it actually means a virtual table. Where is the basic table? With this question, I will refer to my MYPETSHOP database.

As you can see, the above system base table is empty, and the Yellow Crane is always gone, and the white cloud is just a thousand flights... The bigger question is: If there is no basic table, who is the system view in this DB? Isn't that a big flicker ??? But is it true? If you don't see it, it doesn't mean you really don't have it. You can continue to use system view to give us a hand. Next, use sys. objects to find out...

Well, it's probably so much. It's not too early. It's time to take a nap...