Optimize and shorten union query

Below is my query to get a bulk of attributes of some objects. Are there anyway to optimize and shorten it?

SELECT object_id, value, name FROM attr_text WHERE object_id IN ('43', '42', '41', '40', '39') 
UNION 
SELECT object_id, value, name FROM attr_varchar WHERE object_id IN ('43', '42', '41', '40', '39') 
UNION
SELECT object_id, value, name FROM attr_int WHERE object_id IN ('43', '42', '41', '40', '39') 
UNION
SELECT object_id, value, name FROM attr_decimal WHERE object_id IN ('43', '42', '41', '40', '39') 
UNION
SELECT object_id, value, name FROM attr_datetime WHERE object_id IN ('43', '42', '41', '40', '39') 

Results:

43   red    color1
43   blue   color2
43   small  size
42   black  color1
42   big    size

The most obvious improvement you could make is to use a sensible schema.

Assuming that's not an option, one thing I would suggest is changing UNION to UNION ALL as you probably don't have have multiple attributes with the same name and value. The UNION [DISTINCT] just performs unnecessary comparisons. In any case you aren't handling attributes with the same name and different values in your current query.

I too would suggest that the best improvement you could make is to use a sane schema. To elaborate, it'll greatly simplify your SQL and improve performance if you can define a discrete list of columns for your objects, like so:

create table [Object] (
    [object_id] [int] IDENTITY(1,1) NOT NULL,
    [color1] [varchar](255) null,
    [color2] [varchar](255) null,
    [size] [varchar](255) null
    ... any other properties.... 
)

alter table Object
add constraint PK_object_id
primary key clustered (
    object_id
)

Then, you can just select all your objects using a simple select:

select * from object where object_id in ( '43', '42', '41', '40', '39' )