System view of SQL Server

first, understand the system view

1. System view: It is known from the name, which is the storage of some SQL Server system information.

2. Presence Location:

Here, you can see that both "System database" and "user database" have these system views, and one eye sweep down the hair

Now the name is the same.

　　　　

3. System View use:

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 name of this system view, the word is that it is a view to save the table schema, and there are the "rank", "default" attributes for this field.

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, so I can't

To find it, in the system view can be a quick way to 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, however, you can use the system stored procedures, inside the "sp_helptext".

　　

　　

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, the definition of System view location

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, system view exactly where to come from? This question you only ask the SQL Server team, they put system view into a hidden resource database, where is this database? I'll find you.

　　

After finding it, I'll continue to attach it now, and if you're smart enough, you can't just load it, or you'll be reporting that the process is in use.

　　

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

　　

After the attach succeeds, view the attached database view, which is not under the System View folder, but is really the user view.

　　

2. System View Data source

Since it is a view, it is actually a virtual table, since it is a virtual table, then where is the base table? Take a look at our user database. The system table is empty.

　　

As you can see, the System base table above is empty, and where is the data found in this DB view? Because do not see does not mean really no, you can use the system view to wish us a helping hand, and then use sys.objects to explore ... A total of three system tables

　　

System view of SQL Server