How do I find a default constraint using INFORMATION SCHEMA

Question

I m trying to test if a given default constraint exists  I don t want to use the sysobjects table  but the more standard INFORMATION SCHEMA   I ve used this to check for tables and primary key constraints before  but I don t see default constraints anywhere   Are they not there   I m using MS SQL Server 2000    EDIT  I m looking to get by the name of the constraint

User · Accepted Answer

As I understand it  default value constraints aren t part of the ISO standard  so they don t appear in INFORMATION SCHEMA  INFORMATION SCHEMA seems like the best choice for this kind of task because it is cross-platform  but if the information isn t available one should use the object catalog views  sys    instead of system table views  which are deprecated in SQL Server 2005 and later   Below is pretty much the same as  user186476 s answer  It returns the name of the default value constraint for a given column   For non-SQL Server users  you need the name of the default in order to drop it  and if you don t name the default constraint yourself  SQL Server creates some crazy name like  DF TableN Colum 95AFE4B5   To make it easier to change your schema in the future  always explicitly name your constraints    -- returns name of a column s default value constraint  SELECT     default constraints name FROM      sys all columns          INNER JOIN     sys tables         ON all columns object id   tables object id          INNER JOIN      sys schemas         ON tables schema id   schemas schema id          INNER JOIN     sys default constraints         ON all columns default object id   default constraints object id  WHERE          schemas name    dbo      AND tables name    tablename      AND all columns name    columnname

User · Answer

Necromancing   If you only need to check if a default-constraint exists   default-constraint s  may have different name in poorly-managed DBs    use INFORMATION SCHEMA COLUMNS  column default     IF NOT EXISTS      SELECT   FROM INFORMATION SCHEMA COLUMNS     WHERE  1 1       AND TABLE SCHEMA    dbo       AND TABLE NAME    T VWS PdfBibliothek       AND COLUMN NAME    PB Text      AND COLUMN DEFAULT IS NOT NULL     BEGIN      EXECUTE  ALTER TABLE dbo T VWS PdfBibliothek                  ADD CONSTRAINT DF T VWS PdfBibliothek PB Text DEFAULT  N  image    FOR PB Text            END    If you want to check by the constraint-name only    -- Alternative way   IF OBJECT ID  DF CONSTRAINT NAME    D   IS NOT NULL  BEGIN     -- constraint exists  deal with it  END    And last but not least  you can just create a view called INFORMATION SCHEMA DEFAULT CONSTRAINTS    CREATE VIEW INFORMATION SCHEMA DEFAULT CONSTRAINTS  AS  SELECT       DB NAME   AS CONSTRAINT CATALOG       csch name AS CONSTRAINT SCHEMA      dc name AS CONSTRAINT NAME       DB NAME   AS TABLE CATALOG       sch name AS TABLE SCHEMA       syst name AS TABLE NAME       sysc name AS COLUMN NAME       COLUMNPROPERTY sysc object id  sysc name   ordinal   AS ORDINAL POSITION       dc type desc AS CONSTRAINT TYPE       dc definition AS COLUMN DEFAULT       --  dc create date      --  dc modify date  FROM sys columns AS sysc -- 46918   3892 with inner joins   where  -- FROM sys all columns AS sysc -- 55429   3892 with inner joins   where   INNER JOIN sys tables AS syst      ON syst object id   sysc object id   INNER JOIN sys schemas AS sch     ON sch schema id   syst schema id   INNER JOIN sys default constraints AS dc      ON sysc default object id   dc object id  INNER JOIN sys schemas AS csch     ON csch schema id   dc schema id   WHERE  1 1   AND dc is ms shipped   0      WHERE  1 1   AND sch name    dbo  AND syst name    tablename  AND sysc name    columnname

User · Answer

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

User · Answer

Object Catalog View   sys default constraints  The information schema views INFORMATION SCHEMA are ANSI-compliant  but the default constraints aren t a part of ISO standard  Microsoft SQL Server provides system catalog views for getting information about SQL Server object metadata   sys default constraints system catalog view used to getting the information about default constraints   SELECT so object id TableName         ss name AS TableSchema         cc name AS Name         cc object id AS ObjectID                       sc name AS ColumnName         cc parent column id AS ColumnID         cc definition AS Defination         CONVERT BIT                 CASE cc is system named                    WHEN 1                    THEN 1                    ELSE 0                END  AS IsSystemNamed         cc create date AS CreationDate         cc modify date AS LastModifiednDate FROM sys default constraints cc WITH  NOLOCK       INNER JOIN sys objects so WITH  NOLOCK  ON so object id   cc parent object id      LEFT JOIN sys schemas ss WITH  NOLOCK  ON ss schema id   so schema id      LEFT JOIN sys columns sc WITH  NOLOCK  ON sc column id   cc parent column id                                                AND sc object id   cc parent object id ORDER BY so name           cc name

User · Answer

Is the COLUMN DEFAULT column of INFORMATION SCHEMA COLUMNS what you are looking for

User · Answer

WHILE EXISTS       SELECT   FROM  sys all columns      INNER JOIN sys tables ST  ON all columns object id   ST object id     INNER JOIN sys schemas ON ST schema id   schemas schema id     INNER JOIN sys default constraints ON all columns default object id   default constraints object id     WHERE      schemas name    dbo      AND ST name    MyTable    BEGIN  DECLARE  SQL NVARCHAR MAX    N     SET  SQL      SELECT TOP 1       ALTER TABLE      schemas name           ST name      DROP CONSTRAINT     default constraints name          FROM        sys all columns           INNER JOIN       sys tables ST          ON all columns object id   ST object id           INNER JOIN        sys schemas          ON ST schema id   schemas schema id           INNER JOIN       sys default constraints          ON all columns default object id   default constraints object id     WHERE           schemas name    dbo        AND ST name    MyTable             PRINT  SQL    EXECUTE sp executesql  SQL      --End if Error     IF   ERROR  lt  gt  0     BREAK END

User · Answer

A bit of a cleaner way to do this  SELECT DC  name    FROM  sys   default constraints  AS DC   WHERE DC  parent object id    OBJECT ID   Schema   TableName

User · Answer

I am using folllowing script to retreive all defaults  sp binddefaults  and all default constraint with following scripts   SELECT      t name AS TableName  c name AS ColumnName  SC COLUMN DEFAULT AS DefaultValue  dc name AS DefaultConstraintName FROM       sys all columns c     JOIN sys tables t ON c object id   t object id     JOIN sys schemas s ON t schema id   s schema id     LEFT JOIN sys default constraints dc ON c default object id   dc object id     LEFT JOIN INFORMATION SCHEMA COLUMNS SC ON  SC TABLE NAME   t name AND SC COLUMN NAME   c name  WHERE      SC COLUMN DEFAULT IS NOT NULL     --WHERE t name      and c name

User · Answer

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   b 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

User · Answer

If you want to get a constraint by the column or table names  or you want to get all the constraints in the database  look to other answers  However  if you re just looking for exactly what the question asks  namely  to  test if a given default constraint exists     by the name of the constraint   then there s a much easier way   Here s a future-proof answer that doesn t use the sysobjects or other sys tables at all   IF object id  DF CONSTRAINT NAME    D   IS NOT NULL BEGIN    -- constraint exists  work with it  END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How do I find a default constraint using INFORMATION_SCHEMA?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to information-schema