We used "sa_get_table_definition" system procedure for a long time in SA 11.0.1. After upgrading to SA16 we have noticed that Foreign Keys information is no longer present. Only Self-keys are present in the results of sa_get_table_definition. Maybe a bug? Should I open an Incident? Thanks. |
Looks like there's ---------------------------------------------------------------- SELECT @@VERSION; SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' ); @@VERSION '11.0.1.3158' CREATE TABLE "GROUPO"."SalesOrderItems" ( "ID" integer NOT NULL ,"LineID" smallint NOT NULL ,"ProductID" integer NOT NULL ,"Quantity" integer NOT NULL ,"ShipDate" date NOT NULL ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) ) ; COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 'individual items that make up the sales orders' ; GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "GROUPR" ; GRANT SELECT,INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "GROUPRW" ; commit work ; ALTER TABLE "GROUPO"."SalesOrderItems" ADD NOT NULL FOREIGN KEY "FK_ProductID_ID" ("ProductID" ASC) REFERENCES "GROUPO"."Products" ("ID") ; ALTER TABLE "GROUPO"."SalesOrderItems" ADD NOT NULL FOREIGN KEY "FK_ID_ID" ("ID" ASC) REFERENCES "GROUPO"."SalesOrders" ("ID") ON DELETE CASCADE ; commit work ; ---------------------------------------------------------------- SELECT @@VERSION; SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' ); @@VERSION '12.0.1.4231' CREATE TABLE "GROUPO"."SalesOrderItems" ( "ID" integer NOT NULL ,"LineID" smallint NOT NULL ,"ProductID" integer NOT NULL ,"Quantity" integer NOT NULL ,"ShipDate" date NOT NULL ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) ) ; COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 'individual items that make up the sales orders' ; GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "GROUPR" ; GRANT SELECT,INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "GROUPRW" ; commit work ; commit work ; ---------------------------------------------------------------- SELECT @@VERSION; SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' ); @@VERSION '16.0.0.2344' CREATE TABLE "GROUPO"."SalesOrderItems" ( "ID" integer NOT NULL ,"LineID" smallint NOT NULL ,"ProductID" integer NOT NULL ,"Quantity" integer NOT NULL ,"ShipDate" date NOT NULL ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) ) ; COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 'individual items that make up the sales orders' ; GRANT INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "MODIFY_ROLE" ; GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "READ_ROLE" ; commit work ; commit work ; ---------------------------------------------------------------- SELECT @@VERSION; SELECT sa_get_table_definition ( 'GROUPO', 'SalesOrderItems' ); @@VERSION '17.0.7.3399' CREATE TABLE "GROUPO"."SalesOrderItems" ( "ID" integer NOT NULL ,"LineID" smallint NOT NULL ,"ProductID" integer NOT NULL ,"Quantity" integer NOT NULL ,"ShipDate" date NOT NULL ,CONSTRAINT "SalesOrderItemsKey" PRIMARY KEY ("ID" ASC,"LineID" ASC) ) ; COMMENT ON TABLE "GROUPO"."SalesOrderItems" IS 'individual items that make up the sales orders' ; GRANT INSERT,DELETE,UPDATE ON "GROUPO"."SalesOrderItems" TO "MODIFY_ROLE" ; GRANT SELECT ON "GROUPO"."SalesOrderItems" TO "READ_ROLE" ; commit work ; commit work ; |
This issue has been fixed in 16.0.0.2549 and 17.0.8.4043. The sa_get_table_definition built-in system procedure should return the SQL statements required to create the specified table and its indexes, foreign keys, triggers, and granted privileges. |
The question to the audience, is this topic relevant for this question: Running pre-16.0 system procedures as invoker or definer?
I checked an I´m using the DEFINER model:
"SELECT IF ((HEXTOINT(SUBSTRING(DB_PROPERTY('Capabilities'),1,LENGTH(DB_PROPERTY('Capabilities'))-20)) & 8) = 8) THEN 1 ELSE 0 END IF"
It returned 0.