INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

developer tip

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

optionbox 2020. 8. 7. 08:19

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

주어진 기본 제약 조건이 존재하는지 테스트하려고합니다. sysobjects 테이블을 사용하고 싶지 않지만보다 표준적인 INFORMATION_SCHEMA를 사용합니다.

이전에 테이블 및 기본 키 제약 조건을 확인하는 데 사용했지만 기본 제약 조건이 어디에도 표시되지 않습니다.

거기에 없나요? (저는 MS SQL Server 2000을 사용하고 있습니다).

편집 : 제약의 이름을 찾고 있어요.

내가 이해하는대로 기본값 제약 조건은 ISO 표준의 일부가 아니므로 INFORMATION_SCHEMA에 나타나지 않습니다. INFORMATION_SCHEMA는 크로스 플랫폼이기 때문에 이러한 종류의 작업에 가장 적합한 선택처럼 보이지만 정보를 사용할 수없는 경우 SQL Server에서 더 이상 사용되지 않는 시스템 테이블 뷰 대신 개체 카탈로그 뷰 (sys. *)를 사용해야합니다. 2005 년 이후.

아래는 @ user186476의 답변과 거의 같습니다. 주어진 열에 대한 기본값 제약 조건의 이름을 반환합니다. (SQL Server가 아닌 사용자의 경우이를 삭제하려면 기본값의 이름이 필요합니다. 기본 제약 조건의 이름을 직접 지정하지 않으면 SQL Server는 "DF_TableN_Colum_95AFE4B5"와 같은 이상한 이름을 만듭니다. 변경하기 쉽도록하기 위해 스키마는 항상 명시 적으로 제약 조건을 지정하십시오!)

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

다음을 사용하여 기본 제약 조건이 상호 관련된 테이블 이름 및 열 이름을 지정하여 결과를 더욱 좁힐 수 있습니다.

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'

Information_Schema보기 에 기본 제약 조건 이름이없는 것 같습니다 .

SELECT * FROM sysobjects WHERE xtype = 'D' AND name = @name이름으로 기본 제약 조건을 찾는 데 사용

아래 스크립트는 실행중인 데이터베이스의 사용자 테이블에 대한 모든 기본 제약 조건과 기본값을 나열합니다.

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

열 또는 테이블 이름으로 제약 조건을 얻거나 데이터베이스의 모든 제약 조건을 얻으려면 다른 답변을 찾으십시오. 그러나 질문이 정확히 무엇을 요구하는지, 즉 "주어진 기본 제약 조건이 존재하는지 ... 제약 조건의 이름으로 테스트"를 찾는 다면 훨씬 더 쉬운 방법이 있습니다.

다음은 sysobjects또는 다른 sys테이블을 전혀 사용하지 않는 미래 보장형 답변입니다 .

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

INFORMATION_SCHEMA.COLUMNS의 COLUMN_DEFAULT 열이 찾고있는 것입니까?

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

나는 그것이 INFORMATION_SCHEMA에 있다고 생각하지 않습니다-아마도 sysobjects 또는 관련 더 이상 사용되지 않는 테이블 / 뷰를 사용해야 할 것입니다.

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

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

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;

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

참고URL : https://stackoverflow.com/questions/141682/how-do-i-find-a-default-constraint-using-information-schema

'developer tip' 카테고리의 다른 글

투명한 HTML 버튼을 만드는 방법은 무엇입니까? (0)	2020.08.07
ES6 모듈에서 여러 클래스 내보내기 (0)	2020.08.07
클래스와 ID로 요소 내부의 요소 가져 오기-JavaScript (0)	2020.08.07
텍스트가있는 UILabel (0)	2020.08.06
CSS를 사용하여 '필수 필드'별표를 자동으로 추가하여 입력 구성 (0)	2020.08.06

현재글INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

optionbox

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

'developer tip' 카테고리의 다른 글

'developer tip'의 다른글

티스토리툴바

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 어떻게 찾습니까?

'developer tip' 카테고리의 다른 글

'developer tip'의 다른글

관련글

티스토리툴바