Stored Procedure for storing the DDL of Table in a variable

Database
SAP
Enthusiast

Stored Procedure for storing the DDL of Table in a variable

Hi , 

Please help me to create a SP which should get tablename as INPUT variable and store the DDL of the object and drop the table and recreate the same from the variable . I need to use this in place of FLOAD lock .

Thanks in Advance !!!

Tags (1)
3 REPLIES
Senior Apprentice

Re: Stored Procedure for storing the DDL of Table in a variable

Instead of trying to get the full DDL you might simply do:

CREATE TABLE tab_new AS tab_old WITH NO DATA;
DROP TABLE tab_old;
RENAME TABLE tab_new AS tab_old;
SAP
Enthusiast

Re: Stored Procedure for storing the DDL of Table in a variable

Thank you very much dieter . But this will not work for tables with identity column , right ? Please help me in that scenario as well .

Senior Apprentice

Re: Stored Procedure for storing the DDL of Table in a variable

Yep, this approach will fail for tables with identity.

Have a look at Glenn McCall's article Running Unsupported Queries from a Stored Procedure