[sql] Add column to SQL query results

I'm putting together a report in SSRS. The dataset is populated with a SQL query of an MS SQL server. It's querying several similar tables using Union All. The problem is that there's some information loss. The different tables are for different worksites, but none of the columns in those tables has the name of the site; the only way to identify a site is by the table name. In the combined columns which are the result of the Union All, there's no way to tell which rows come from which site.

Is there a way to alter my query to add a column to the results, which would have the worksite with which each row is associated? I can't add this to the original table, because I have read-only permissions. I'd thought of something like this, but I don't know what sort of expression to use, or if it can even be done:

SELECT t1.column, t1.column2
FROM t1
<some expression>
UNION ALL
SELECT t2.column, t2.column2
FROM t2
<some expression>
UNION ALL
...

etc. The expression would 'add' a column, which would add the site name associated with each part of the query. Could this or anything else work to get the site name?

why dont you add a "source" column to each of the queries with a static value like

select 'source 1' as Source, column1, column2...
from table1

UNION ALL

select 'source 2' as Source, column1, column2...
from table2