Default Value in System Table?

[NWChowd]

Hi There,

I am looking for where the default value for columns is stored in the system tables. I can't find it anywhere, but probably just missed it.


Thanks,
NWChowd

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[DrSql]

Tyr this..

select * from INFORMATION_SCHEMA.COLUMNS

Dr.Sql
Good Luck.

 

[NWChowd]

thanks.

I am working on a vendor-developed database in which they used SQL Server Defaults for some columns. So when I use the INFORMATION_SCHEMA.COLUMNS view, I get (for example):
/****** Object: Default dbo.active Script Date: 3/1/2000 8:50:15 PM ******/ create default active as 1

rather than the value of 1, which I would like.

So, my next question is: Is there any way to return the actual value of the defined default value?

Thanks,
NWChowd

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[DrSql]

Can you clarify what you are looking, may be provide an example.

Dr.Sql
Good Luck.

 

[NWChowd]

In essence, I am trying to develop a rudimentary data dictionary. I would like the following information:

Tables
Table Description
Primary Keys
Foreign Keys
Columns
Indexes
Data Types
Allow Nulls
Default Values
Description


I believe I have captured everything except default values.
Here's the query I'm using:

SELECT
o.[id] AS 'TableID',
o.[name] AS 'TableName',
c.colorder AS 'ColumnOrder',
c.[name] AS 'ColumnName',
t.[name] + '(' + CAST(c.length AS VARCHAR(4)) + ')' AS 'ColumnDatatype',
CASE c.isnullable
WHEN 0 THEN 'Yes'
ELSE 'No'
END AS 'ColumnAllowNulls',

I.Column_Default AS 'ColumnDefaultValue',
e.value AS 'ColumnDescription'
FROM sysobjects o
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN systypes t on c.xtype = t.xtype
INNER JOIN INFORMATION_SCHEMA.COLUMNS I ON o.[name] = I.Table_NAME and c.[name] = I.COLUMN_NAME
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table', @table_name, N'column', null) e on c.name = e.objname
WHERE o.name = @table_name
ORDER BY c.colorder


======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[gmmastros]

I don't understand your question about default values since you are already returning the 'Column_Default' from information_Schema.Columns

The reason for my post is that you appear to have a mistake in your ColumnAllowNulls field. You are pulling this info form the syscolumns table when it is already available in the information_schema.columns view. The illustrate my point, add the Is_Nullable column from information_schema.columns.

SELECT
    o.[id]                 AS 'TableID',
        o.[name]                 AS 'TableName',
        c.colorder             AS 'ColumnOrder',
        c.[name]                 AS 'ColumnName',
    t.[name] + '(' + CAST(c.length AS VARCHAR(4)) + ')'         AS 'ColumnDatatype',
    CASE c.isnullable
        WHEN 0 THEN 'Yes'
        ELSE 'No'
    END                    AS 'ColumnAllowNulls',
[red]	i.Is_Nullable    As 'IsNullable',[/red]
    I.Column_Default        AS 'ColumnDefaultValue',
        e.value                 AS 'ColumnDescription' 
FROM     sysobjects o 
    INNER JOIN syscolumns c ON o.id = c.id
    INNER JOIN systypes t on c.xtype = t.xtype
    INNER JOIN INFORMATION_SCHEMA.COLUMNS I ON o.[name] = I.Table_NAME and c.[name] = I.COLUMN_NAME
    LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
        N'user',N'dbo',N'table', @table_name, N'column', null) e on c.name = e.objname
    WHERE o.name = @table_name 
    ORDER BY c.colorder

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NWChowd]

Hi George,
Thanks for the comments.

Regarding your confusion, the result I get for the ColumnDefaultValue is literally:
"/****** Object: Default dbo.active Script Date: 3/1/2000 8:50:15 PM ******/ create default active as 1"


In the case of this default, what I would like to have is the actual value of "1" since that is the value for the default named "active".

So, the value being returned is the big long string, rather than the actual value.

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[DrSql]

You should be able case syntax in your sql statement.

select
defaultStatus=case when default=1 then 'Active'
when default=0 then 'InActive'
end
Is this what you are looking?


Dr.Sql
Good Luck.

 

[DrSql]

Try to execute this on PUBS db.
[tt]
select
TABLE_NAME,COLUMN_NAME, COLUMN_DEFAULT,
IS_NULLABLE, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME
[/tt]

Dr.Sql
Good Luck.