Best way to find default value for column in Sql server

Question

I want to find the default value for a column in my SQL2005 database. I only have a connection string to the SQL server, so I can only use the Sql library namespace (not use the Oledb GetSchema stuff). I know there is GetSchema () ... but how can I use it to get the column layout and therefore the default values? I cannot populate Schema on a datatable because it does not seem to populate the default property. I can use "exec sp_columns" ADDRESS_MASTER "and return the default values, but they are not in a pure format ... for example, one of my text columns returns by default as" (N'test) ".

I also want to get the size of the column ... sp_columns seems to give me everything ... but I don't know how to decode the default values ​​in all cases?

Answer 1

Your problem here in the default value is the expression, and it seems like you want it to just return a value such as "bob" or 5.

This pretty much depends on when you use GetDate () or call a custom function like dbo.MyFunc (). At what stage should it be returned as the default?

, sql, - , , sql , , :

DECLARE @def nvarchar(max)

SELECT @def = column_def FROM information_schema.columns
WHERE table_name = 'ADDRESS' AND column_name = 'ADDRESS_MASTER'

EXEC ('SELECT ' + @def)

, , , GetDate, . , , , , .

Answer 2

. , , . , 1 int, sp_columns column_def ((1)). , .

, , , sp_columns :

Declare @TableName varchar(255); 
Set @TableName = 'Table';

Declare @ColumnName varchar(255); 
Set @ColumnName = 'Column';

Declare @ColumnDefinition Table 
(
    TABLE_QUALIFIER  sysname null,
    TABLE_OWNER      sysname null,
    TABLE_NAME       sysname null,
    COLUMN_NAME      sysname null,
    DATA_TYPE        sysname null,
    TYPE_NAME        sysname null,
    PRECISION        int null, 
    LENGTH           int null,
    SCALE            int null,
    RADIX            int null,
    NULLABLE         bit null,
    REMARKS          nvarchar(4000) Null,
    COLUMN_DEF       sysname  null,
    SQL_DATA_TYPE    int null,
    SQL_DATETIME_SUB int null,
    CHAR_OCTET_LENGTH int null,
    ORDINAL_POSITION  int null,
    IS_NULLABLE       char(3) null,
    SS_DATA_TYPE      int null
)

Insert Into @ColumnDefinition
Exec sp_columns @TableName, @column_name = @ColumnName

Select COLUMN_DEF From @ColumnDefinition

Answer 3

select * from INFORMATION_SCHEMA.Columns

?

Answer 4

SMO ( SQL Server) . Microsoft.SqlServer.* ( , - ), - :

Server myServer = new Server("servername");
Database myDatabase = myServer.Databases["myDatabaseName"];
Table myTable = myDatabase.Tables["myTableName"];
Column myColumn = myTable.Columns["myColumnName"];

string defaultValue = myColumn.Default;

Answer 5

To get the default constraint value for a specific column, do the following:

SELECT defaults.definition
FROM sys.all_columns AS cols
INNER JOIN sys.default_constraints AS defaults ON cols.default_object_id = defaults.object_id
WHERE cols.object_id = @TABLEID AND cols.[name] = @COLNAME

where @TABLEID is just the table identifier, and @COLNAME is the name of the desired column in the database.