Finding all "DEFAULT" constraints on a table using Information_Sch

I have a couple stored procedures in which I am trying to determine all of
the "Default" constraints that are on a given table. I know that this
information is available in the sysobjects table, but I'm trying to avoid
directly querying the system tables whenever possible in favor of using the
Information Schema views. My problem is that the Table_Constraints view
appears to show only CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE constraints
.
Is there any other Information Schema view that I can use to see the DEFAULT
constraints?information_schema.columns. Will have the default values.
Hope this helps.|||Try using system table sysconstraints.
Example:
use northwind
go
select
object_name([id]) as table_name,
object_name(constid) as constraint_name,
col_name([id], colid) as column_name
from
sysconstraints
where
[id] = object_id('dbo.orders')
and objectproperty(constid, 'IsDefaultCnst') = 1
go
AMB
"DoubleBlackDiamond" wrote:

> I have a couple stored procedures in which I am trying to determine all of
> the "Default" constraints that are on a given table. I know that this
> information is available in the sysobjects table, but I'm trying to avoid
> directly querying the system tables whenever possible in favor of using th
e
> Information Schema views. My problem is that the Table_Constraints view
> appears to show only CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUE constrain
ts.
> Is there any other Information Schema view that I can use to see the DEFA
ULT
> constraints?
>sql

Filtering SqlDataSource to show all vs. non-null records

Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (?) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance.

Bill

Construct a better SELECT that uses a parameter.

SELECT ...

FROM ...

WHERE (@.ShowAll=1)

OR (@.ShowAll=0 AND (col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL))

|||Sweet. Thank you much.

Filtering SQL statement with datepart

Hi!

Im making a page where I want to display this weeks information & news as a default setting.

What do I have to do to make it work (Im new to .Net but used to ASP 3 & MySql)?

My idea is to store the week number in a variable which is updated att Page_load and then
to use this variable in the following sql statement that gets news and info from the MsSql
database where weeknumber is = the defined variable.

So how do I store weeknumber in a variable?
AND
How do I use this variable in the SQL statement?

Weeknumber can be store in a Session variable.

There is a function in SqlServer called DateDiff() with many options, you can get the number of week between 2 date. than compare this nubmer with the Session variable.

Hope this help