# SHOW CREATE PROCEDURE

## Syntax

```sql
SHOW CREATE PROCEDURE proc_name
```

## Description

This statement is a MariaDB extension. It returns the exact string that can be used to re-create the named [stored procedure](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures), as well as the [SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode) that was used when the trigger has been created and the character set used by the connection.. A similar statement, [SHOW CREATE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-function), displays information about [stored functions](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions).

Both statements require that:

* you are the owner of the routine;
* you have the [SHOW CREATE ROUTINE](https://mariadb.com/docs/server/reference/account-management-sql-statements/grant#database-privileges) privilege (from [MariaDB 11.3.0](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/11.3/11.3.0)); or
* you have the [SELECT](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) privilege on the [mysql.proc](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-proc-table) table.

When none of the above statements are true, the statements display `NULL` for the `Create Procedure` or `Create Function` field.

{% hint style="danger" %}
Users with `SELECT` privileges on [mysql.proc](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-proc-table) or `USAGE` privileges on `*.*` can view the text of routines, even when they do not have privileges for the function or procedure itself.
{% endhint %}

{% tabs %}
{% tab title="Current" %}
`SHOW CREATE PROCEDURE` quotes identifiers, according to the value of the [sql\_quote\_show\_create](https://kb-archive.mariadb.net/kb/en/server-system-variables/#sql_quote_show_create) system variable.
{% endtab %}

{% tab title="< 10.6.5 / 10.5.13 / 10.4.22" %}
`SHOW CREATE PROCEDURE` quotes identifiers, according to the value of the [sql\_quote\_show\_create](https://kb-archive.mariadb.net/kb/en/server-system-variables/#sql_quote_show_create) system variable. The output of this statement is unreliably affected by the [sql\_quote\_show\_create](https://kb-archive.mariadb.net/kb/en/server-system-variables/#sql_quote_show_create) system variable.
{% endtab %}
{% endtabs %}

## Examples

Here's a comparison of the `SHOW CREATE PROCEDURE` and [SHOW CREATE FUNCTION](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-function) statements.

```sql
SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
           Procedure: simpleproc
            sql_mode: 
    Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
                      BEGIN
                      SELECT COUNT(*) INTO param1 FROM t;
                      END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode:
     Create Function: CREATE FUNCTION `hello`(s CHAR(20))
                      RETURNS CHAR(50)
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
```

When the user issuing the statement does not have privileges on the routine, attempting to [CALL](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/call) the procedure raises Error 1370.

```bash
CALL test.prc1();
Error 1370 (42000): execute command denied to 
  user 'test_user'@'localhost' for routine 'test'.'prc1'
```

If the user neither has privilege to the routine nor the [SELECT](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) privilege on [mysql.proc](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-proc-table) table, it raises Error 1305, informing them that the procedure does not exist.

```sql
SHOW CREATE TABLES test.prc1\G
Error 1305 (42000): PROCEDURE prc1 does not exist
```

## See Also

* [Stored Procedure Overview](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/stored-procedure-overview)
* [CREATE PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/create-procedure)
* [ALTER PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/alter-procedure)
* [DROP PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/drop-procedure)
* [SHOW PROCEDURE STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-procedure-status)
* [Stored Routine Privileges](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges)
* [Information Schema ROUTINES Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-routines-table)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-procedure.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.