T-SQL (SS2K8) :: Create Dynamics View Which Contain Data Of All Table

I have view something like

Create view All_employee
AS
SELECT Emp_Name, Emp_code FROM dbo.Employee
UNION ALL
SELECT Emp_Name, Emp_code FROM Emp_201402.Employee

But we have a different "Schema" for same table because we have archive table with same table name but with different schema name. Now we have req to make view which contain data of all table. But I can't seem to figure out how to do it in a view.

SET NOCOUNT ON
DECLARE @Count INT, @TotalCount INT, @SQL VARCHAR( MAX )
DECLARE @Schema TABLE ( ID INT, NAME VARCHAR(512) )
INSERT INTO @Schema
SELECT ROW_NUMBER() OVER (ORDER BY SCHEMA_ID), Name FROM sys.schemas where name like '%emloyee%' ORDER BY schema_id ASC

[Code] ....

Don' think that works.

Is this possible with a view or it other way to do it?