select @@version
go
create default test.def_test as 123
go
select name, type_desc, parent_object_id
from sys.objects where object_id=object_id('test.def_test')
go
create table test.table_test1 (n int)
go
create table test.table_test2 (n int)
go
exec sp_bindefault 'test.def_test', 'test.table_test1.n'
go
exec sp_bindefault 'test.def_test', 'test.table_test2.n'
go
insert into test.table_test1 default values;
insert into test.table_test2 default values;

select * from test.table_test1
union all
select * from test.table_test2;

select schema_name(t.schema_id) schema_name, t.name
from sys.columns c
join sys.tables t on t.object_id=c.object_id
where c.default_object_id=object_id('test.def_test')
go

drop table test.table_test1
go
drop table test.table_test2
go
drop default test.def_test
go

-----------------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.5069.00 (X64) 
	Aug 22 2012 18:02:46 
	Copyright (c) 1988-2005 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)


(1 row affected)

name                type_desc           parent_object_id
------------------- ------------------- ----------------
def_test            DEFAULT_CONSTRAINT  0

(1 row affected)

Default bound to column.
Default bound to column.

(1 row affected)

(1 row affected)
n
-----------
123
123

(2 rows affected)

schema_name   name
------------- -------------
test          table_test1
test          table_test2

(2 rows affected)