Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    47

    Unanswered: sysconstraints and status column

    How to interprete the colum status in sysconstraints table ?
    (I have some thing like 133141).

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah didn't M$ do a FANTASTIC job with the system catalog.....

    Use the views in INFORMATION_SCHEMA

    And for a kick go look at sp_help sproc code....I always get a chuckle....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    47
    In infromation schema, I didn't find the view in which column default constraint is defined.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check this one:

    CREATE TABLE [dbo].[test2] (
    [id] [int] NOT NULL ,
    [id2] [int] NOT NULL ,
    [col1] [varchar] (10) COLLATE Latin1_General_BIN NULL
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[test2] WITH NOCHECK ADD
    CONSTRAINT [PK_test2] PRIMARY KEY CLUSTERED
    (
    [id],
    [id2]
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[test2] ADD
    CONSTRAINT [CK_test2] CHECK ([id] >= (-5) and [id] <= 4)
    GO
    select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='test2'
    select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='test2'
    go
    drop table [dbo].[test2]

    Just create a join and you'll get it...

  5. #5
    Join Date
    Oct 2003
    Posts
    47
    Thanks ... and I agree with you for your example. But try this one :

    CREATE TABLE dbo.T_AlarmsLog
    (
    Id int CONSTRAINT DF_T_AlarmsLog_Id DEFAULT 0 NOT NULL,
    AlarmId int CONSTRAINT DF_T_AlarmsLog_AlarmId DEFAULT 0 NULL,
    ActionType int CONSTRAINT DF_T_AlarmsLog_ActionType DEFAULT 0 NULL,
    Name varchar(255) NULL,
    Comment text NULL,
    TriggerDate datetime NULL,
    Location varchar(255) NULL,
    IsAcquitted int CONSTRAINT DF_T_AlarmsLog_IsAcquitted DEFAULT 0 NULL,
    CurrentTaskId int CONSTRAINT DF_T_AlarmsLog_CurrentTaskId DEFAULT 0 NULL,
    CurrentTaskIdAttributedByTheServer int CONSTRAINT DF_T_AlarmsLog_CurrentTaskIdAttributedByTheServer DEFAULT 0 NULL,
    CurrentProcessId int CONSTRAINT DF_T_AlarmsLog_CurrentProcessId DEFAULT 0 NULL,
    CurrentProcessIdAttributedByTheServer int CONSTRAINT DF_T_AlarmsLog_CurrentProcessIdAttributedByTheServ er DEFAULT 0 NULL,
    UserId int CONSTRAINT DF_T_AlarmsLog_UserId DEFAULT 0 NULL,
    UserName varchar(255) NULL,
    UserIdentifier varchar(255) NULL,
    CompanyId int CONSTRAINT DF_T_AlarmsLog_CompanyId DEFAULT 0 NULL,
    CompanyName varchar(255) NULL,
    CompanyIdentifier varchar(255) NULL,
    JoinedFile varchar(255) NULL,
    ActionsTriggered int NULL,
    LastUpdateDate datetime NULL,
    LastUpdateUser varchar(100) NULL,
    lngShowUser int NULL,
    CONSTRAINT PK_T_AlarmsLog
    PRIMARY KEY NONCLUSTERED (Id)
    ON [PRIMARY]
    )


    in which information schema table will we find details about constraints by the name of :

    DF_T_AlarmsLog_CurrentProcessIdAttributedByTheServ er (default constraint defined under a column) ?

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    For defaults use this

    select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='table'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •