Shulou(Shulou.com)05/31 Report--

Sql Server how to use the system view, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

One: those system views

1. What is the system view for?

You can tell from the name, is the system view? If you make a good guess, you will store some information about the sqlserver system. Good. Congratulations, you got it right.

two。 Where are all the definitions?

In order to make you believe what you see, take a look at the screenshot below. From the screenshot, you can see that both "system database" and "user database" have these system views, and at a glance, they even have the same name.

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

Q1: when I was maintaining a system, I only knew that there was a table field called "state" in a database, but I forgot to define it in that table. How do I find out?

A1: this is simple. There is a system view called "INFORMATION_SCHEMA.COLUMNS" in sqlserver. Let's take a screenshot below.

As can be seen from the word SCHEMA in the name of the system view, it turns out that it is a view that saves the table schema, and there are such features as "ranking" and "default value" of this field, peat, does it have a good feeling?

Q2: I see a stored procedure name "CategoryInsert" in the C # code. I want to see its source code, but there are thousands of stored procedures in my table, so I can't find them one by one., please, is there a quick way to check it in the system view?

A2:so easy . I'll tell you what, only what you can't think of, it can't be done without a system view, isn't it just a simple look at the stored procedure code? Sys.sql_modules can help you achieve it.

Q3: this method is good, but the definition field of copy is not formatted. Brother, thousands of lines of sql. What about the output that I really want to format? Thank you.

A3: indeed, as you said, formatting the output, the system View can only help you so far, but there is no other way, you can use the system stored procedure, there is a magical sp_helptext, you can wish you a dream come true, you are welcome.

Second: some thoughts on the system view

In the above code, I demonstrated the benefits brought to us by two system view, one proc. If you take a closer look, you will have two doubts.

1: where is the system View defined?

This is a good question to ask. From the beginning of the article, we know that my user library MYPETSHOP has a lot of system view, but I really don't define these view. God can testify to me, then the question is very mysterious, where does system view come from? This is a question you can only ask the sqlserver team, they put the system view into a hidden resource database, so where is the database? I'll find it for you.

After finding it, I will continue to add it now. If you are smart enough, you can't load it directly, otherwise you will report that the process is in use. I think you also know the reason.

The solution is also very simple. Let's make a copy to E disk. And then attach this copy.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, 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.