Exporting SQL views

Hi there,

The export functionality is awesome. It would be great if we don't include schema name when exporting SQL views to another database.

Many thanks

Can anyone reply to my concern please? It's quiet not convenient when exporting SQL views to another database. Which actually still pointing to original database.

If there is any setting available then please guide.

Many thanks

Are you on the latest version, 10.1?

I get this when I export a view from database "test" to "test2":

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
  VIEW `test2`.`v` AS SELECT * from countries ;

No source database name in that query.

Thanks for your reply.

Yes, I am using latest version 10.1.0.5464.

Please check my screenshots of how I am getting this scenario.

Many thanks

Ok, I see the view code has the old database name.

And how does the export SQL code look like which HeidiSQL generates?

I assume the server adds that database name.

Where can I see this? I'm using output to another database option.

If I use clipboard export option then this is the sql code:

-- Host: localhost -- Server version: 5.7.25-0ubuntu0.16.04.2 - (Ubuntu) -- Server OS: Linux -- HeidiSQL Version: 10.1.0.5464

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET NAMES utf8 /; /!50503 SET NAMES utf8mb4 /; /!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /; /!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;

-- Dumping database structure for Test1 CREATE DATABASE IF NOT EXISTS Test1 /!40100 DEFAULT CHARACTER SET latin1 /; USE Test1;

-- Dumping structure for view Test1.vw_customer -- Creating temporary table to overcome VIEW dependency errors CREATE TABLE vw_customer ( CustomerId INT(11) NOT NULL, Name VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci', Address VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci' ) ENGINE=MyISAM;

-- Dumping structure for view Test1.vw_customer -- Removing temporary table and create final VIEW structure DROP TABLE IF EXISTS vw_customer; CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW vw_customer AS select Customer.CustomerId AS CustomerId,Customer.Name AS Name,Customer.Address AS Address from Customer;

/!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') /; /!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) /; /!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;

Many thanks

You can see the view structure in your log:

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER
VIEW vw_customer AS
select Customer.CustomerId AS CustomerId,Customer.Name AS Name,Customer.Address AS Address from Customer;

HeidiSQL is at least not to blame here - the old database name obviously gets inserted by the server.

Thanks very much.

Now the question is how to stop server to add old database name?

Please suggest the solution.

Could you please advise a solution for not to add old database name when exporting SLQ views?

I have no advice.

Apart from the fact that HeidiSQL normally works around that dependency issue by creating an exported view at first as a table, and after all other tables are created it drops the fake table and creates the real view. I can only guess you don't have that "Customer" table in the target db.

Customer table is already there. Actually we have a multi tenant software and we often need to export Views to update tenant databases.

having the same problem here.

When I create a view in DB1 like:

select id from tablename

there is no databasename in it.

When I do SHOW CREATE VIEW view1;

as result I get

select id from tablename

when I look in the view1.frm file, it says:

select DB1.tablename.id from DB1.tablename

when I export it to DB2

the result of the view1 in DB2 is

select DB1.tablename.id from DB1.tablename

I dont need the DB1 there.

Is there a way to disable this in mysql/mariadb?