MENU Show Overview Explanation The columns that this view returns are as follows: (Source: SQL Server 2005 Books Online) Here is an example of data that was pulled from the AdventureWorks database. This data was pulled using this query: SELECT*FROM
INFORMATION_SCHEMA.TABLES To only show a list of tables you would use this query: SELECT*
FROMINFORMATION_SCHEMA.TABLES To only show a list of only the view you would use this query: SELECT*FROMINFORMATION_SCHEMA.TABLES Last Update: 7/2/2009 Comments For This ArticleThe best way how to explain what the INFORMATION_SCHEMA database is would be – “This is the database about databases. It’s used to store details of other databases on the server”. What does that mean, how we can use it, and what we can do with this data is the topic of today’s article. The ModelAs always, we’ll start with the data model first. This is the same model we’ve used so far in this series, so I won’t describe it again.
 Still, one thing is interesting. Today we won’t be interested in the data stored in tables, but rather how this model is described in the INFORMATION_SCHEMA database. The INFORMATION_SCHEMA database is an ANSI standard set of views we can find in SQL Server, but also MySQL. Other database systems also have either exactly such or similar database implemented. It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…) stored on the server. You could easily use this data to:
Therefore, this database could prove to be very useful in some cases, especially if you’re in the DBA role Listing All DatabasesMaybe the first logical thing to do is to list all databases which are currently on our server. We can do it in a few ways. While these two are not directly related to the usage of the INFORMATION_SCHEMA and are SQL Server-specific, let’s look at them first.
You can easily notice that the first query returns many more details (several columns outside this pic) than the second query. It uses a SQL Server-specific sys object. While this works great, it’s very specific, so I’ll go into detail in a separate article. The second statement is the execution of the system stored procedure sp_databases which returns the predefined columns. Using INFORMATION_SCHEMA to Access Tables DataSince this database is an ANSI standard, the following queries should work in other DBMS systems as well. We’ll list all tables in the database we’ve selected and also all constraints. To do that, we’ll use the following queries:
The first important thing is that, after the keyword USE, we should define the database we want to run queries on. The result is expected. The first query lists all tables from our database, while the second query returns all constraints, we’ve defined when we created our database. In both of these, besides their name and the database schema they belong to, we can see many other details. It’s important to notice that in constraints we also have the TABLE_NAME column, which tells us which table this constraint is related to. We’ll use that fact to relate tables INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_CONSTRAINTS to create our custom query. Let’s take a look at the query as well as its’ result.
No doubt this query looks cool. Still, let’s comment on a few things. With this query, we’ve:
Maybe you’re asking yourself why would you do something like this. Well, with minor modifications to this query, you can easily count a number of keys in each table. Let’s do that.
From the result, we can now easily notice the number of keys/constraints in all tables. This way we could find tables:
Queries like this one could be a part of controls checking is everything ok with your database. You could complicate things even more and use this query as a subquery for a more complex query that will automatically test predefined errors/alerts/warnings. The INFORMATION_SCHEMA TablesIt would be hard to try out every single table and show what it returns. At least, that would be hard to put into one readable article. I strongly encourage you to play with the INFORMATION_SCHEMA database and explore what is where. The only thing I’ll do here is to list all the tables (views) you have at disposal. They are:
ConclusionQuerying the INFORMATION_SCHEMA database provides you with a lot of options on how to control the changes in the structure of your database as well to implement some level of automation on the database layer. In order to achieve these two, you should follow some rules, like naming convention, internal data modeling rules, etc. You could also exploit it to document your database. Later in this series, we’ll talk more about that. Table of contents
Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic |
Cara menggunakan information_schema mysql w3schools
Pos Terkait
Copyright © 2024 idkuu.com Inc.
