Get table name from union query? This is my query

SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename3 where Active =1


It is working properly.

Now I want to fetch the table name for the respective productName in future.


So how can I fetch it?


I tried AS.. like this:   

SELECT Id, productName, Largeimagepath, Discount, Price, Image
FROM tablename3 AS tablename
where Active = 1;


but didn't get output.


How shall I correct the query and also Improve the performance of query?
Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to:

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;


This will return a new column called Source with an identifier that shows which table the row came from. 

As @ypercube suggested in the comments you may also want to consider altering this to a UNION ALL - this will include duplicate but you'll also have an identifier on which table it came from. Using a UNION ALL will eliminate the performance impact of removing dups. 

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;