Why can't I see all the code from a procedure when using get_ddl or describe?

user_4454613 0
Viewed 699

This is my first posted question here so I hope the format is acceptable.

I am able to execute get_ddl() and DESC PROCEDURE for all of our stored procedures but some have a null body when using describe and are cut short when using the get_ddl(). If I connect with the user that is the owner of the SP it does show me the whole procedure. I have also granted all privileges on the procedures to my ETL user in the schema and I still can't see all the code.

This is the result of running get_ddl() on the SP, it is missing the sql command variable and everything after:

CREATE OR REPLACE PROCEDURE "SP_test"()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '';

I am able to call the SP with a user that is not the owner and it works correctly. The account admin role was also not able to see the full procedure for all the SP's.

Can someone give me a hand?

Thanks, James

snowflake-cloud-data-platform stored-procedures
2 Answers

This may be because Snowflake supports SP overloading. What you may be seeing is an early copy of the SP before it had the variables added. It may have been added accidentally without any contents. For example:

-- Someone creates the shell of an SP like this:
create or replace procedure foo()
returns string
language javascript
as
$$
$$;

-- Later someone adds one or more variables and a body
create or replace procedure foo(MY_VARIABLE string)
returns string
language javascript
as
$$
    return "Hello world.";
$$;

-- Get the old signature, body is empty
select get_ddl('procedure', 'foo()');
describe procedure foo();

-- Get the newer signature, body has contents
select get_ddl('procedure', 'foo(string)');
describe procedure foo(string);
user_127563810

I've got this issue too, and after search I found its a permission problem. in snowflake USAGE permission does not enable read procedure body, only the definition of name and parameters. so grant read permission to your user and you will see the content in GET_DDL() command, DESC procedure, SHOW procedure, etc. you can see your permission using SHOW GRANT TO USER USER_NAME or SHOW GRANT TO ROLE ROLE_NAME.

user_152731340
Related