We want to store backup of table structure into a table every week . What would be the best way to achieve this.
For example we want to get the table structure for all the tables in one particular database using show table and then store that information into a table
If you have a process to maintain the tables without ALTER (Backup data, Drop & Recreate for changes), you could just directly use the 'RequestText' value from DBC.Tables
SEL DatabaseName, TABLENAME, RequestTextFROM DBC.TablesWHERE DatabaseName = <DatabaseName>AND TableKind = 'T'