TLDR: where are the procedure_id, view_id, and table_id?
Long version: I'm trying to create object chain mapping for a business development application. I need to specify objects in a stream of actions so we can map out which objects are being used, which aren't, and I need a way to identify objects that are being used by multiple other objects. Sort of an "impacts map" or something.
I was visualizing a parent/child keystore where object ID could be used but was dismayed to find that information_schema didn't return that for procedures, tables, or views. I've done a little digging and all I can find is query_ids which doesn't help.
Maybe I can make my own clustered index or something, but I really don't want to reinvent something that's probably already there under the hood.
*EDIT
Returning to this now that I have the attention: creating my own index attempt results in not having enough access:
CREATE INDEX IDX_PROCEDURE_ID ON
INFORMATION_SCHEMA.procedures (
PROCEDURE_CATALOG,
PROCEDURE_SCHEMA,
PROCEDURE_NAME,
ARGUMENT_SIGNATURE
);
Error:
Insufficient privileges to operate on view 'PROCEDURES'
Is nothing easy?
TLDR: where are the procedure_id, view_id, and table_id?
Long version: I'm trying to create object chain mapping for a business development application. I need to specify objects in a stream of actions so we can map out which objects are being used, which aren't, and I need a way to identify objects that are being used by multiple other objects. Sort of an "impacts map" or something.
I was visualizing a parent/child keystore where object ID could be used but was dismayed to find that information_schema didn't return that for procedures, tables, or views. I've done a little digging and all I can find is query_ids which doesn't help.
Maybe I can make my own clustered index or something, but I really don't want to reinvent something that's probably already there under the hood.
*EDIT
Returning to this now that I have the attention: creating my own index attempt results in not having enough access:
CREATE INDEX IDX_PROCEDURE_ID ON
INFORMATION_SCHEMA.procedures (
PROCEDURE_CATALOG,
PROCEDURE_SCHEMA,
PROCEDURE_NAME,
ARGUMENT_SIGNATURE
);
Error:
Insufficient privileges to operate on view 'PROCEDURES'
Is nothing easy?
Internal system identifiers can be found inside SNOWFLAKE.ACCOUNT_USAGE views:
TABLE_ID - NUMBER - Internal, Snowflake-generated identifier for the table.
Example:
SELECT TABLE_ID, TABLE_SCHEMA_ID, TABLE_CATALOG_ID
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES
WHERE deleted IS NULL;