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 !!!
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;
Thank you very much dieter . But this will not work for tables with identity column , right ? Please help me in that scenario as well .
Yep, this approach will fail for tables with identity.
Have a look at Glenn McCall's article Running Unsupported Queries from a Stored Procedure