SQL Server Tour-First stop the system view that brings us benefits

　　

Originally wanted to write something about this series, and then think of everyone as a programmer, the most used is the database, but in fact a lot like me to work in the first line of the code farm, on the SQL

All smattering, don't talk about optimization and the database of the bottom of the understanding, I also like this ...

One: those system views

1. What does the system view do?

You know from the name, System view? Guess good, is to store some of the SQL Server System information, very good, congratulations, correct.

2. Where are the definitions?

In order for you to see the truth, let's look at it, as you can see, whether it is "system database" or "user database" have these system views, and one eye sweep down the hair

Now the name is the same.

3. See what benefits these system views can bring to me?

Q1: When I was maintaining a system, I only knew that there was a database in which a table field was called "State", but I forgot to define it in that table? I should like

How to find out?

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

From this schema in the system view name, the word is that the original is a view of the table schema, and there is the "rank", "default" Properties of this field, peat,

Is there a kind of cool feeling???

Q2: I see a stored procedure name "Categoryinsert" in C # code, I want to see its source code, but there are thousands of stored procedures in my table, a total can't let me

go to the find it ,,, bye. is There a quick way to view it in the system view?

A2:so Easy ... Tell you, only you can not think of, no system view to do, not a simple look at the stored procedure Code it?

Sys.sql_modules can help you achieve it.

Q3: This method is good, but copy's definition field is not formatted .... Big Brother, thousands of rows of sql Oh ... I especially want to format the loss

What to do? Thank you, sir.

A3: Indeed as you say, formatted output, System view can only help you here, but despair, you can use the system stored procedures, there is a

the magical sp_helptext, can wish you to realize your dream, no thanks.

　　

　　

Second: Some thoughts on the system view

In the code above, I demonstrated two system view, a proc to bring us benefits, then look closely, you will have two doubts ....

1: Where is the system view defined?

This question is very good, from the beginning of the article we know that my user base mypetshop is a lot of system view, but I really do not define these view Ah, God can

Give me a testimony, the question is mysterious, where does system view come from? You only have to ask the SQL Server team, they put system view in a hidden

Resource database, where is this database? I'll find you.

Once found, I continue to attach now, if you are smart enough, you can not directly load it, otherwise it will be reported that the process is in use, the reason 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 is impatient to see, careful you through the following, I think you should understand what, these views are not in the "System View"

Folder below, but really as a user view ... Right...

2: Where is the data source for system view?

This is also a very classic question, since it is the view, I think we all understand, in fact, it is the meaning of virtual table, since it is a virtual table, then where is the base table? With this question, I'll turn it over.

My Mypetshop database.

It can be seen that the above system base table is empty, yellow crane is gone, the Baiyun thousand empty leisurely ... The bigger question is, if the base table is not there, then in this DB

Who does system view look for? Isn't that a big trick??? But is the truth true? Because you don't see it doesn't mean it's not true, you can continue to use System view to wish me

We have a hand, then use sys.objects to find out ...

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

SQL Server Tour-First stop the system view that brings us benefits