When you deal with old databases, with origins somewhere around second half of nineteen nineties, you can expect all kinds of oddities, like funny naming conventions, (lack of) referential integrity, absence of clustered indexes and things like rules and defaults created separately from tables DDL.
I just happen to work with such database. I see many tables which some time ago were migrated from a Sybase system that have their DEFAULT constraints defined with sp_bindefault stored procedure. You don’t have to memorize exact syntax of the invocation of this procedure because it is marked as obsolete and should not be used anymore:
This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you create default definitions by using the DEFAULT keyword of the ALTER TABLE or CREATE TABLE statements instead. For more information, see Creating and Modifying DEFAULT Definitions.
Now, when I read the documentation, the sp_bindefault has some interesting capability. If you create default and user defined type (alias), and you bind this default to the type with sp_bindefault, you can inadvertently affect all columns of that type in all tables in the database if they don’t have any default or are using existing default of the alias type:
1: create table dbo.t1
3: col1 int not null,
4: col2 int not null constraint DF_t1col2 default(0)
7: ---create default for the column
8: create default dbo.DFValue_1 as 1
10: ---bind default to the column
11: exec sp_bindefault 'dbo.DFValue_1', 'dbo.t1.col1'
13: ---create udt
14: create type ssn
15: from varchar(15) not null
17: --create default - empty string
18: create default dbo.DF_UDTEmpty as ''
20: --bind default to the udt
21: exec sp_bindefault 'dbo.DF_UDTEmpty', 'dbo.ssn'
23: --add new column to the table
24: alter table dbo.t1 add secnumber1 ssn
26: ---insert into the table and select results
27: insert dbo.t1(col1, col2, secnumber1) default values
29: ---secnumber1 is empty string
30: select * from dbo.t1
32: ---create second table
33: create table dbo.t2
35: col1 int constraint DF_t2_col1 default(10),
36: secnumber ssn
39: create default dbo.DF_SSNNotEmpty as '<unknown ssn>'
41: ---note here - no @futureonly param provided
42: exec sp_bindefault 'dbo.DF_SSNNotEmpty', 'dbo.ssn'
44: --insert into t1 and t2
45: insert dbo.t1(col1, col2, secnumber1) default values
46: insert dbo.t2(col1, secnumber) default values
48: ---check contents of the tables
49: select * from dbo.t1 --what?
50: select * from dbo.t2
If you run the above script you will see that the default value of the column secnumber1 in table dbo.t1 has changed to ‘<unknown ssn>’! You can really hurt yourself if you make a mistake and omit the parameter @futureonly in the call to sp_bindefault. If you use @futureonly parameter, the above behavior will not occur. It’s pretty easy to make changes you don’t know about until user calls you at 3 am on Sunday.
It is good idea to change tables to use standard column DEFAULT constraints. This should be of course made with care, probably only when you change the tables anyway. But, if you were curious how many tables you have in your database that use the DEFAULT objects, you can run this script:
1: ---all default objects with names of tables they are assigned to.
2: select b.default_object_id, a.name default_object_name,
3: b.object_id table_id, object_schema_name(b.object_id) table_schema,
4: object_name(b.object_id) table_name, b.name column_name, b.column_id
5: from sys.objects a inner join sys.all_columns b
6: on a.object_id = b.default_object_id
7: where 1=1
8: and a.object_id not in (select object_id from sys.default_constraints)
Full testing script is attached to the post as usual, enjoy :).