Get table name from union 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;

You can try this with UNION ALL

as probably you need duplicate entry too as will adding table name it will be unique and performance will be also improved

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