System view in SQL Server details _mssql

Originally wanted this series to write something good, then think of everyone as a programmer, use the most is the database, but in fact many like me to work in the first line of the code farmers, a little knowledge of SQL, do not talk about optimization and database on the bottom of the understanding, I am also so ...

One: Those system view

1. What does the system view do?

From the first name, you know, system view? A good guess is to store some information about the SQL Server system, good, congratulations, correct.

2. Where is the definition?

In order for you to see the reality, the following screenshot, from the screenshot you can see, whether the "system database" or "user database" have these system views, and a glance to sweep down to find even the same name.

3. What benefits can I get from these system views?

Q1: When I am maintaining a system, I only know that there is a database in which the field of a table is called "state", but I forgot to define it in that table. How am I supposed to find out?

A1: This simple, in SQL Server provides a system view called "INFORMATION_SCHEMA." COLUMNS ", let's take a screenshot below to see.

The word from this schema in the system view name is a view of the schema that holds the table, and the "rank" and "default" Properties of the field, peat, does it have a great feeling???

Q2: I saw a stored procedure name "Categoryinsert" in C # code, I want to see its source, but I have thousands of stored procedures in the table, can not let me one by one to find it,, please in the system view can be a quick way to view?

A2:so Easy ... Tell you, only you can not imagine, no system view can not do, not a simple look at the stored procedure Code? Sys.sql_modules can help you achieve it.

Q3: This method is good, but the copy definition field is not formatted .... Big Brother, thousands of lines of SQL Oh ... What do I really want to do with formatted output? Thank you, sir.

A3: Indeed, as you said, formatted output, the system view can only help you to this, but despair, you can use the system stored procedures, there is a magical sp_helptext, you can wish you realize the dream, do not thank.

　　

Second: Some thoughts on the system view

In the above code, I demonstrated two system view, a proc to bring us benefits, so take a closer look, you will have two of doubts ....

1: Where is the system view defined?

This question is very good, from the beginning of the article we know that my user library mypetshop is a lot of system view, but I really did not define these view, God can testify to me, the question is very mysterious, the system view where exactly come from? This is the only question you can ask the SQL Server team, they put system view into a hidden resource database, where is the database? I'll find it for you.

After I find it, I will continue to attach it now, if you are smart enough, you can't load it directly, otherwise the report process is in use, cause I think you know.

The solution is also very simple, we make a copy to E disk. And then attach this copy just fine.

Since the attached, I now feel just can't wait to see, carefully you through the screenshot below, I think you should understand what, these views are not in the "System View" folder under, but is really as a user view ... Right...

2: Where is the data source for the system view?

This is also a very classic question, since it is view, I think we all understand, in fact, it is the meaning of the virtual table, since it is a virtual table, where is the base table? With this question, I'll flip through my mypetshop database.

Can see, the above system base table is empty, yellow crane is gone forever, the white clouds thousand empty leisurely ... The bigger question is, if you don't have the underlying table, who is the system view in this db? Isn't that a big trick??? But is the truth true? Because you do not see does not mean that there is no, you can continue to use System view to wish us a hand, and then use sys.objects to find out ...

Well, probably say so much, the time is not early, wash and sleep ...