Cara menggunakan stored procedure mysql w3schools


The INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.

This view can be called from any of the databases in an instance of SQL Server and will return the results for the data within that particular database.

The columns that this view returns are as follows:

Column name Data type Description
TABLE_CATALOG nvarchar(128) Table qualifier.
TABLE_SCHEMA nvarchar(128) Name of schema that contains the table.
TABLE_NAME sysname Table name.
TABLE_TYPE varchar(10) Type of table. Can be VIEW or BASE TABLE.

(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:


Cara menggunakan stored procedure mysql w3schools

To only show a list of tables you would use this query:


To only show a list of only the view you would use this query:


Last Update: 7/2/2009

Comments For This Article

The 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 Model

As 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:

  • Check what’s on the server and/or in the database
  • Check if everything is as expected (e.g. compared to the last time you’ve performed this check)
  • Automate processes and build some complex code (e.g. code generators – we’ll talk about this later)

Therefore, this database could prove to be very useful in some cases, especially if you’re in the DBA role

Listing All Databases

Maybe 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 Data

Since 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:


-- list of all tables in the selected database



-- list of all constraints in the selected database



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.


--join tables andconstraints data










No doubt this query looks cool. Still, let’s comment on a few things. With this query, we’ve:

  • Again, we’ve pointed out which database we are using. This could have been avoided if you write <database_name> each time before INFORMATION_SCHEMA, e.g. our_first_database.INFORMATION_SCHEMA.TABLES.TABLE_NAME. I don’t prefer it that way
  • Joined two tables, in the same manner, we would join two “regular” database tables. This is good to know, but, as we’ll see later, you can join many things (system tables, subqueries) and not only “regular” tables
  • We have also ordered our result so we can easily notice all the constraints on each table

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.


--join tables andconstraints data












From the result, we can now easily notice the number of keys/constraints in all tables. This way we could find tables:

  • Without a primary key. This could be the result of an error in the design process
  • Without foreign keys. Tables without foreign keys should be only dictionaries or some kind of reporting table. In all other cases, we should have a foreign key
  • While UNIQUE shouldn’t be related to errors, in most cases we can expect that the table will have only 0 or 1 UNIQUE values. In case there is more, we could check why is that

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.


It 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:

  • CHECK_CONSTRAINTS – details related to each CHECK constraint
  • COLUMN_DOMAIN_USAGE – details related to columns that have an alias data type
  • COLUMN_PRIVILEGES – columns privileges granted to or granted by the current user
  • COLUMNS – columns from the current database
  • CONSTRAINT_COLUMN_USAGE – details about column-related constraints
  • CONSTRAINT_TABLE_USAGE – details about table-related constraints
  • DOMAIN_CONSTRAINTS – details related to alias data types and rules related to them (accessible by this user)
  • DOMAINS – alias data type details (accessible by this user)
  • KEY_COLUMN_USAGE – details returned if the column is related with keys or not
  • PARAMETERS – details related to each parameter related to user-defined functions and procedures accessible by this user
  • REFERENTIAL_CONSTRAINTS – details about foreign keys
  • ROUTINES –details related to routines (functions & procedures) stored in the database
  • ROUTINE_COLUMNS – one row for each column returned by the table-valued function
  • SCHEMATA – details related to schemas in the current database
  • TABLE_CONSTRAINTS – details related to table constraints in the current database
  • TABLE_PRIVILEGES –table privileges granted to or granted by the current user
  • TABLES –details related to tables stored in the database
  • VIEW_COLUMN_USAGE – details about columns used in the view definition
  • VIEW_TABLE_USAGE – details about the tables used in the view definition
  • VIEWS – details related to views stored in the database


Querying 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

Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection
Learn SQL: Dynamic SQL
Learn SQL: How to prevent SQL Injection attacks
  • Author
  • Recent Posts

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

Apa yang dimaksud dengan store procedure pada SQL Server?

Stored procedure ini merupakan suatu blok program yang dapat dipanggil secara berulang-ulang dalam bentuk script. Dengan menggunakan stored procedure maka seluruh aplikasi akan terdistribusi dan ketika mengeksekusi satu perintah CALL aplikasi dapat langsung membentuk sejumlah task yang dapat berjalan di dalam server.

Apa itu stored procedure MySQL?

Stored Procedure adalah sebuah prosedur layaknya subprogram (subrutin) di dalam bahasa pemrograman reguler yang tersimpan di dalam katalog basis data. Beberapa kelebihan yang ditawarkan stored procedure antara lain : mengingkatakan performa, mereduksi trafik jaringan, reusable, dan meningkatkan kontrol sekuriti.

Apa perbedaan dari MySQL dan sql?

Perbedaan utama antara SQL dan MySQL! SQL adalah bahasa query, sedangkan MySQL adalah database relasional yang menggunakan SQL untuk query database. sahabat data dapat menggunakan SQL untuk mengakses, memperbarui, dan memanipulasi data yang disimpan dalam database.