How do I find a default constraint using INFORMATION_SCHEMA?

You can use the following to narrow the results even more by specifying the Table Name and Column Name that the Default Constraint correlates to: select * from sysobjects o inner join syscolumns c on o. Id = c. Cdefault inner join sysobjects t on c.Id = t.

Id where o. Xtype = 'd' and c.Name = 'Column_Name' and t. Name = 'Table_Name.

I search for this simple query since a couple of hours. Thannnnnkkk youuuu! – Samuel Nov 29 at 21:13.

There seems to be no Default Constraint names in the Information_Schema views. Use SELECT * FROM sysobjects WHERE xtype = 'D' AND name = @name to find a default constraint by name.

Is the COLUMN_DEFAULT column of INFORMATION_SCHEMA. COLUMNS what you are looking for?

Yes and no, it tells me there is a default and what it is, but I need the name of the constraint too. – WildJoe Sep 26 '08 at 20:45 Also, be aware that if your runtime SQL login does not own the dbo schema, you may only find NULL values in the COLUMN_DEFAULT column. – Glen Little Feb 22 at 23:10.

The script below lists all the default constraints and the default values for the user tables in the database in which it is being run: SELECT b. Name AS TABLE_NAME, d. Name AS COLUMN_NAME, a.Name AS CONSTRAINT_NAME, c.

Text AS DEFAULT_VALUE FROM sys. Sysobjects a INNER JOIN (SELECT name, id FROM sys. Sysobjects WHERE xtype = 'U') be on (a.

Parent_obj = b. Id) INNER JOIN sys. Syscomments c ON (a.

Id = c. Id) INNER JOIN sys. Syscolumns d ON (d.

Cdefault = a. Id) WHERE a. Xtype = 'D' ORDER BY b.

Name, a.name.

I don't think it's in the INFORMATION_SCHEMA - you'll probably have to use sysobjects or related deprecated tables/views. You would think there would be a type for this in INFORMATION_SCHEMA. TABLE_CONSTRAINTS, but I don't see one.

Probably because on some of the other SQL DBMSs the "default constraint" is not really a constraint, you'll not find its name in "INFORMATION_SCHEMA. TABLE_CONSTRAINTS", so your best bet is "INFORMATION_SCHEMA. COLUMNS" as others have mentioned already.(SQLServer-ignoramus here) The only a reason I can think of when you have to know the "default constraint"'s name is if SQLServer doesn't support "ALTER TABLE xxx ALTER COLUMN yyy SET DEFAULT..." command.

But then you are already in a non-standard zone and you have to use the product-specific ways to get what you need.

How about using a combination of CHECK_CONSTRAINTS and CONSTRAINT_COLUMN_USAGE: select columns. Table_name,columns. Column_name,columns.

Column_default,checks. Constraint_name from information_schema. Columns columns inner join information_schema.

Constraint_column_usage usage on columns. Column_name = usage. Column_name and columns.

Table_name = usage. Table_name inner join information_schema. Check_constraints checks on usage.

Constraint_name = checks. Constraint_name where columns. Column_default is not null.

Select c. Name, col. Name from sys.

Default_constraints c inner join sys. Columns col on col. Default_object_id = c.

Object_id inner join sys. Objects o on o. Object_id = c.

Parent_object_id inner join sys. Schemas s on s. Schema_id = o.

Schema_id where s. Name = @SchemaName and o. Name = @TableName and col.Name = @ColumnName.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.