08 April 2019

Over the years, I have needed to generate SQL commands for every table or for every column. Below are two examples, one for Sybase and one for MariaDB

  SELECT CONCAT('DROP TABLE ', table_name, ';') FROM information_schema.tables where table_schema='vanilla71';
  SELECT 'ALTER TABLE ' + sysobjects.name + ' modify ' + syscolumns.name + ' null;',
        syscolumns.status, syscolumns.cdefault, syscolumns.*
  FROM sysobjects
  JOIN syscolumns ON sysobjects.id = syscolumns.id
  JOIN systypes ON systypes.type = syscolumns.type AND systypes.usertype = syscolumns.usertype
  WHERE sysobjects.uid = user_id('your_user') AND status=0 AND cdefault=0
  order by sysobjects.name, syscolumns.name;

Less Is More ~ Older posts are available in the archive.