8 April 2019

Making SQL Commands from SQL Commands

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;
tags: sql - mariadb - sybase - table - column

Less Is More