Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.

Exporting SQL views

anuragnz's profile image anuragnz posted 6 months ago in General

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

anuragnz's profile image anuragnz posted 6 months ago

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

ansgar's profile image ansgar posted 6 months ago

Are you on the latest version, 10.1?



Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.
ansgar's profile image ansgar posted 6 months ago

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.

anuragnz's profile image anuragnz posted 6 months ago

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

3 attachment(s):
  • Screenshot_13
  • Screenshot_14
  • Screenshot_15
ansgar's profile image ansgar posted 6 months ago

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.

anuragnz's profile image anuragnz posted 6 months ago

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

ansgar's profile image ansgar posted 6 months ago

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.

anuragnz's profile image anuragnz posted 6 months ago

Thanks very much.

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

Please suggest the solution.

anuragnz's profile image anuragnz posted 6 months ago

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



Ads were blocked by your webbrowser.

This is not a problem, but keep in mind that developing HeidiSQL, user support and hosting takes time and money.

You may want to send a donation instead.
ansgar's profile image ansgar posted 6 months ago

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.

anuragnz's profile image anuragnz posted 6 months ago

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

Please login to leave a reply, or register at first.