I'm writing a script that analyses database tables and I'm having problems with fetching the default value of columns from SQL Server 2005.
The following works fine on SQL Server 2008, but when I run the same thing on SQL Server 2005, the result is an empty string, and I can't figure out why.
DECLARE @sDatabase VARCHAR(100); DECLARE @sTable VARCHAR(100); DECLARE @sColumn VARCHAR(100); SET @sDatabase = 'SomeDatabase'; SET @sTable = 'tbl_something'; SET @sColumn = 'SomethingID'; SELECT syscolumns.name AS column_name, systypes.name column_type, syscolumns.length AS column_length, syscolumns.prec AS column_precision, syscolumns.scale AS column_scale, syscolumns.isnullable AS column_nullable, syscomments.text AS column_default_value -- Should be default value! FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id INNER JOIN systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN syscomments ON syscomments.id = syscolumns.cdefault WHERE sysobjects.xtype = 'U' AND sysobjects.name = @sTable AND systypes.name != 'sysname' ORDER BY sysobjects.name, syscolumns.colid
Another version is as follows, but it's the same story; returns an empty string on SQL Server 2005, but the correct value on SQL Server 2008.
SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = @sDatabase AND TABLE_NAME = @sTable AND COLUMN_NAME = @sColumn
This is used for comparing certain changes between databases. Originally I thought that the problem was because SQL Server 2005 returned default values such as "((0))" while SQL Server 2008 was returning "(0)" for the same default value of the integer 0. That's what SQL Server Management Studio reports respectively. But upon further investigation, it turns out that the default values of SQL Server 2005 are simply not being displayed at all, so it is always recorded as a change when in fact there is none.
Any help deeply appreciated.