Comparing 2 Data Base objects

Hi Team
is there any tool to compare 2 database objects in memsql i am using dbeaver it has the option to have a simple compare but not showing all the options I need to check is there a table or procedure change in both schemes…

What kind of comparison do you want to do, exactly?

I expect that any tool that looks at information_schema information about tables and columns (basic mysql-compatible stuff) will work to find changes like adding a new column, but it would understand things like columnstore indexes in SingleStore, so it won’t be a complete solution.

There is nothing out there we know of that can compare a SingleStore schema for changes in stored procedures or functions.

MySQL workbench or TOAD might be able to compare basic objects, like tables, for changes.

yes like compare like table structure and procedure available from both scheme… like
Dev to Prod 2 schema comparing

Okay, I understand what you’re looking for.

is there any tool? to compare and to the schema

I wrote a schema compare/update utility for my company because I couldn’t find such a utility elsewhere. Unfortunately, I can’t share all of the code, but basically, after you enumerate your database objects, you can call SHOW CREATE [object] on the source and target databases, then compare the DDL.

For objects that exist in the source db but not the target, you can simply execute the CREATE statement DDL from the source on the target database. For objects that exist in the target db but not the source, you can execute a DROP statement on the target database.

For functions, views, and stored procedures, if there’s a difference in the DDL, you can simply drop and create the object in the target database (because there’s no data loss with those objects). For tables, CREATE (if exists in source but not target) and DROP (if exists in target but not source) are simple, but if there are differences requiring ALTER TABLE statements, that requires more logic.

Here are a few queries that might help you get started. I use these queries to enumerate the database objects:

        public List<string> GetTables(string database)
        {
            string sql = String.Format("SELECT TABLE_NAME AS `Item` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME;", database);
            return GetItemsFromQuery(sql);
        }

        public List<string> GetViews(string database)
        {
            // For some reason (perhaps because functions are referenced in some of our 
            // sprocs in JOIN statements) some of our functions are being returned in the 
            // query for views. So, strip out anything that says it's a view but has a function 
            // with a matching name.
            string sql = String.Format("SELECT v.TABLE_NAME AS `Item` FROM INFORMATION_SCHEMA.VIEWS v WHERE v.TABLE_SCHEMA = '{0}' AND NOT EXISTS(SELECT f.ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES f WHERE v.TABLE_NAME = f.ROUTINE_NAME) ORDER BY v.TABLE_NAME;", database);
            return GetItemsFromQuery(sql);
        }

        public List<string> GetStoredProcedures(string database)
        {
            string sql = String.Format("SELECT ROUTINE_NAME AS `Item` FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME;", database);
            return GetItemsFromQuery(sql);
        }

        public List<string> GetFunctions(string database)
        {
            string sql = String.Format("SELECT ROUTINE_NAME AS `Item` FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME;", database);
            return GetItemsFromQuery(sql);
        }

Hope this helps. Best, Kevin

2 Likes