All Forums Database
SAP 73 posts Joined 08/14
30 Nov 2014
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 !!!

SAP
dnoeth 4628 posts Joined 11/04
30 Nov 2014

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;

 

Dieter

SAP 73 posts Joined 08/14
30 Nov 2014

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

SAP

dnoeth 4628 posts Joined 11/04
30 Nov 2014

Yep, this approach will fail for tables with identity.
Have a look at Glenn McCall's article Running Unsupported Queries from a Stored Procedure

Dieter

You must sign in to leave a comment.