Column Default Values from sys tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Column Default Values from sys tables

Author

Topic

achobbs
Starting Member

11 Posts

Posted - 2004-09-29 : 11:37:38

I am trying to get the column defaults for columns in a table. By defaults I am trying to get the actual values, not what the default is. For example, if the column default is getdate(), I don't want getdate(), I want "9/28/2004 ...".

I have written the following query:

select
'select ' + scm.text + ' as ' + sc.name
from
syscomments scm
inner join
syscolumns sc
on sc.cdefault = scm.id
inner join
sysobjects so
on sc.id = so.id
where
so.name = 'mydeftest'

which returns a select statement that selects the default as the column name and I then loop through the statements, executing each one and getting the value back.

Does anyone know of an easier way to get these values?

As an example, if I have the following table:

table1
(
col1 int default 1,
col2 datetime default getdate(),
col3 uniqueidentifier default newid(),
col4 char(1) default 'Y'
)

I want to be able to get a resultset or somehow get the following info:

col1 = 1
col2 = "9/28/2004 ..."
col3 = "34433-3434df-jhfds3..."
col4 = 'Y'

So that when a user inserts a record and doesn't specify a value I can insert the default value.

Thanks,

Ashton Hobbs
ApexSQL Edit - Lead Developer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 12:05:01

for that you don't need to get default values like that.
use variables:
declare @col1 datetime
set @col1 = getdate()
declare @col2 uniqueidentifier
set @col2 = newid()

and use those in the insert

Go with the flow & have fun! Else fight the flow

achobbs
Starting Member

11 Posts

Posted - 2004-09-29 : 12:45:01

The problem is that I don't know the table beforehand, the user can dynamically choose the table and I then have to determine which columns have defaults and then we they do an insert into the table, what value should be inserted.

Thanks,

Ashton

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-29 : 12:50:12

but even if the user chooses the table dynmically, the default values will still be inserted.
that's the whole point of a default value. or am i missing something here???

Go with the flow & have fun! Else fight the flow

achobbs
Starting Member

11 Posts

Posted - 2004-09-29 : 14:19:31

This is for a custom built editing application that uses ADO.NET and the CommandBuilder. The CommandBuilder generates a generic SQL insert statement of the form (insert into table (...) values (...))

I don't control the insert statement and by default it lists all values and simply inserts null for any values not provided by the user (be nice if it simply didn't list the column if no value was specified).

I currently handle the RowUpdating event when doing an insert so that I can catch which columns have a default and had no value specified by the user and then I set the value to what I determined the default to be for the column.

I am able to get the default using the process I described originally but was wondering if there was a better way.

Thanks,

Ashton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources