![]() REFERENTIAL_CONSTRAINTS – details about foreign keys PARAMETERS – details related to each parameter related to user-defined functions and procedures accessible by KEY_COLUMN_USAGE – details returned if the column is related with keys or not They are:ĬHECK_CONSTRAINTS – details related to each CHECK constraintĬOLUMN_DOMAIN_USAGE – details related to columns that have an alias data typeĬOLUMN_PRIVILEGES – columns privileges granted to or granted by the current userĬOLUMNS – columns from the current databaseĬONSTRAINT_COLUMN_USAGE – details about column-related constraintsĬONSTRAINT_TABLE_USAGE – details about table-related constraintsĭOMAIN_CONSTRAINTS – details related to alias data types and rules related to them (accessible by this user)ĭOMAINS – alias data type details (accessible by this user) The only thing I’ll do here is to list all the tables (views) you have at disposal. I strongly encourage you to play with the INFORMATION_SCHEMA database and explore what is where. At least, that would be hard to put into one readable article. It would be hard to try out every single table and show what it returns. Things even more and use this query as a subquery for a more complex query that will automatically test predefined Queries like this one could be a part of controls checking is everything ok with your database. In case there is more, we could check why is that While UNIQUE shouldn’t be related to errors, in most cases we can expect that the table will have only 0 or 1 In all other cases, we should have a foreign key Tables without foreign keys should be only dictionaries or some kind of reporting table. This could be the result of an error in the design process TABLE_NAME ASC įrom the result, we can now easily notice the number of keys/constraints in all tables. Server-specific, let’s look at them first. While these two are not directly related to the usage of the INFORMATION_SCHEMA and are SQL Maybe the first logical thing to do is to list all databases which are currently on our server. Therefore, this database could prove to be very useful in some cases, especially if you’re in the DBA role code generators – we’ll talk about this later) compared to the last time you’ve performed this check)Īutomate processes and build some complex code (e.g. ![]() It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…) stored on the server.Ĭheck what’s on the server and/or in the databaseĬheck if everything is as expected (e.g. Other database systems also have either exactly such or similar database implemented. The INFORMATION_SCHEMA database is an ANSI standard set of views we can find in SQL Server, but also MySQL. Model is described in the INFORMATION_SCHEMA database. Today we won’t be interested in the data stored in tables, but rather how this This is the same model we’ve used so far in this series, so I What does that mean, how we can use it, and what we can do with this data is the topic of today’s article.Īs always, we’ll start with the data model first. It’s used to store details of other databases on the server”. The best way how to explain what the INFORMATION_SCHEMA database is would be – “This is the database about databases.
0 Comments
Leave a Reply. |