Jak znaleźć domyślne ograniczenie za pomocą INFORMATION_SCHEMA?

Próbuję sprawdzić, czy istnieje dane ograniczenie domyślne. Nie chcę używać tabeli sysobjects, ale bardziej standardową wersję INFORMATION_SCHEMA.

Używałem tego wcześniej do sprawdzania tabel i ograniczeń klucza podstawowego, ale nigdzie nie widzę ograniczeń domyślnych.

Czy ich tam nie ma? (Używam MS SQL Server 2000).

EDYCJA: szukam nazwy ograniczenia.

Jak rozumiem, ograniczenia wartości domyślnych nie są częścią normy ISO, więc nie pojawiają się w INFORMATION_SCHEMA. INFORMACJA_SCHEMA wydaje się najlepszym wyborem dla tego rodzaju zadań, ponieważ jest to wieloplatformowe, ale jeśli informacje nie są dostępne, należy użyć widoków katalogu obiektów (sys. *) Zamiast widoków tabeli systemowej, które są przestarzałe w SQL Server 2005 i później.

Poniżej znajduje się prawie to samo, co odpowiedź @ user186476. Zwraca nazwę ograniczenia wartości domyślnej dla danej kolumny. (W przypadku użytkowników innych niż SQL Server, potrzebujesz nazwy domyślnej, aby ją usunąć, a jeśli nie nazwiesz domyślnego ograniczenia samodzielnie, SQL Server utworzy szaloną nazwę, taką jak „DF_TableN_Colum_95AFE4B5”. Aby ułatwić zmianę swój schemat w przyszłości, zawsze jawnie nazwij swoje ograniczenia!)

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

Aby jeszcze bardziej zawęzić wyniki, można skorzystać z następujących opcji, określając nazwę tabeli i nazwę kolumny, z którą koreluje domyślne ograniczenie:

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'

Wydaje się, że w Information_Schemawidokach nie ma nazw domyślnych ograniczeń .

użyj, SELECT * FROM sysobjects WHERE xtype = 'D' AND name = @name aby znaleźć domyślne ograniczenie według nazwy

Poniższy skrypt zawiera listę wszystkich domyślnych ograniczeń i wartości domyślnych dla tabel użytkowników w bazie danych, w której jest uruchamiany:

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

Jeśli chcesz uzyskać ograniczenie według nazw kolumn lub tabel albo chcesz uzyskać wszystkie ograniczenia w bazie danych, poszukaj innych odpowiedzi. Jeśli jednak szukasz dokładnie tego, o co chodzi w pytaniu, a mianowicie, aby „sprawdzić, czy dane ograniczenie domyślne istnieje ... pod nazwą ograniczenia” , istnieje znacznie łatwiejszy sposób.

Oto odpowiedź na przyszłość, która w ogóle nie korzysta z tabel sysobjectsani innych systabel:

IF object_id('DF_CONSTRAINT_NAME', 'D') IS NOT NULL BEGIN
   -- constraint exists, work with it.
END

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

Czy kolumna COLUMN_DEFAULT w sekcji INFORMATION_SCHEMA.COLUMNS jest tym, czego szukasz?

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 <> 0 
   BREAK
END 

Nekromancja.
Jeśli chcesz tylko sprawdzić, czy istnieje ograniczenie
domyślne ( ograniczenia domyślne mogą mieć inną nazwę w źle zarządzanych bazach danych),
użyj 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 

Jeśli chcesz sprawdzić tylko nazwę ograniczenia:

-- Alternative way: 
IF OBJECT_ID('DF_CONSTRAINT_NAME', 'D') IS NOT NULL 
BEGIN
    -- constraint exists, deal with it.
END 

I wreszcie, możesz po prostu utworzyć widok o nazwie
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'
*/

Wydaje mi się, że nie jest to w pliku INFORMATION_SCHEMA - prawdopodobnie będziesz musiał użyć sysobjects lub powiązanych przestarzałych tabel / widoków.

Można by pomyśleć, że będzie taki typ w INFORMATION_SCHEMA.TABLE_CONSTRAINTS, ale ja go nie widzę.

Prawdopodobnie dlatego, że w niektórych innych bazach danych SQL „domyślne ograniczenie” nie jest tak naprawdę ograniczeniem, nie znajdziesz jego nazwy w „INFORMATION_SCHEMA.TABLE_CONSTRAINTS”, więc najlepszym rozwiązaniem jest „INFORMATION_SCHEMA.COLUMNS”, jak inni już wspominali.

(Tutaj SQLServer-ignoramus)

Jedynym powodem, dla którego przychodzi mi do głowy, kiedy musisz znać nazwę „domyślnego ograniczenia”, jest to, że SQLServer nie obsługuje "ALTER TABLE xxx ALTER COLUMN yyy SET DEFAULT..."polecenia. Ale wtedy jesteś już w niestandardowej strefie i musisz użyć specyficznych dla produktu sposobów, aby uzyskać to, czego potrzebujesz.

Co powiesz na użycie kombinacji CHECK_CONSTRAINTS i 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

Używam następującego skryptu, aby odzyskać wszystkie domyślne (sp_binddefaults) i wszystkie domyślne ograniczenia z następującymi skryptami:

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 = ''

Widok katalogu obiektów : sys.default_constraints

Widoki schematu informacji INFORMATION_SCHEMAsą zgodne z ANSI, ale domyślne ograniczenia nie są częścią normy ISO. Microsoft SQL Server udostępnia widoki katalogu systemowego do uzyskiwania informacji o metadanych obiektów SQL Server.

sys.default_constraints widok katalogu systemowego używany do uzyskiwania informacji o domyślnych ograniczeniach.

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;