programing

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 찾으려면 어떻게 해야 합니까?

goodsources 2023. 4. 26. 23:19
반응형

INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 찾으려면 어떻게 해야 합니까?

지정된 기본 제약 조건이 있는지 테스트하려고 합니다.sysobjects 테이블을 사용하지 않고 더 표준적인 INFORMATION_SCHEMA를 사용하고 싶습니다.

테이블과 기본 키 제약 조건을 확인하는 데 사용한 적이 있지만 기본 제약 조건은 없습니다.

MS SQL Server 2000을 사용하고 있습니다.

편집: 제약 조건의 이름을 알고 싶습니다.

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

아래는 @user186476의 답변과 거의 같습니다.지정된 열에 대한 기본값 제약 조건의 이름을 반환합니다. (SQL Server가 아닌 사용자의 경우 기본값 제약 조건을 삭제하려면 기본값의 이름이 필요하며, 사용자가 직접 이름을 지정하지 않으면 SQL Server는 "DF_TableN_Colum_95"와 같은 비정상적인 이름을 생성합니다.AFE4B5".나중에 스키마를 쉽게 변경하려면 항상 제약 조건의 이름을 명시적으로 지정하십시오!)

-- 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의 COLUMN_DEFAULT 열입니다.찾고 있는 열이 무엇입니까?

네크로맨싱.
기본 제약 조건이 있는지만 확인하면 되는 경우
DB에서 수 .)
INFORMATION_SCHEMA 파일입니다.◦(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

제약 조건 이름으로만 확인하려는 경우:

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

마지막으로 중요한 것은 다음과 같은 보기를 만들 수 있다는 것입니다.
INFORMATION_SCHEMA 파일_SCHEMA. CONSTARINGS: DEFAULT_ 제약조:

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

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에는 없는 것 같습니다. 시스템 개체나 사용하지 않는 관련 테이블/뷰를 사용해야 할 것입니다.

INFORMATION_SCHEMA에 이에 대한 유형이 있을 것이라고 생각할 수 있습니다.표_제약사항입니다. 그런데 하나도 안 보이네요.

다른 일부 SQL DBMS에서는 "기본 제약 조건"이 실제로 제약 조건이 아니기 때문에 "INFORMATION_SCHEMA"에서 해당 이름을 찾을 수 없습니다.TABLE_CONSTRINGS"를 선택하면 "정보_SCHEMA"를 선택할 수 있습니다.다른 사람들이 이미 언급한 것처럼 COLUMNS".

(여기서는 SQL Server-ignoramus

constraint의 이름을 할 때 할 수 가 " 본약제의조건"를 지원하지 않는 경우입니다."ALTER TABLE xxx ALTER COLUMN yyy SET DEFAULT..." 하지만 이미 비표준 한 것을 얻기 위해 제품별 방법을 해야 합니다하지만 이미 비표준 영역에 있으므로 필요한 것을 얻기 위해 제품별 방법을 사용해야 합니다.

CHECK_CONSTARGS와 CONSTARTION_COLUM_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

다음 스크립트를 사용하여 모든 기본값(sp_binddefaults)과 모든 기본 제약 조건을 검색합니다.

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

개체 카탈로그 보기 : sys.default_constraints

뷰는 "정보 스키마 뷰"입니다.INFORMATION_SCHEMAANSI 호환이지만 기본 제약 조건은 ISO 표준의 일부가 아닙니다.Microsoft SQL Server는 SQL Server 개체 메타데이터에 대한 정보를 가져올 수 있는 시스템 카탈로그 보기를 제공합니다.

sys.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;

이를 위한 좀 더 깔끔한 방법:

SELECT DC.[name]
  FROM [sys].[default_constraints] AS DC
  WHERE DC.[parent_object_id] = OBJECT_ID('[Schema].[TableName]')

대상 데이터베이스에 100만 개 이상의 개체가 있는 경우 다음을(를) 사용합니다.sys.default_constraints 시 90시%+로을 줄 수 .sys.syscolpars다음에 대한 키 검색이 수행됩니다.dflt당신은 아마 신경 쓰지 않을 것입니다.DB에서 실제로 스캔한 나머지 I/O 장애 1.12MM 행의 158개 읽기에서 4개 행만 조립하는 데 1.129초가 걸립니다.

현재 시스템 사용으로 변경합니다.테이블/뷰, @Tim의 쿼리를 사용하여 동일한 4개의 제약 조건이 2ms 내에 획득됩니다.누군가가 제가 팀의 것을 찾은 것만큼 유용하다고 생각하기를 바랍니다.

SELECT ConstraintName = sdc.name
     , SchemaName     = ssch.name
     , TableName      = stab.name
     , ColumnName     = scol.name
  FROM sys.objects            sdc
       INNER JOIN sys.columns scol
               ON scol.default_object_id = sdc.object_id
       INNER JOIN sys.objects stab
               ON stab.object_id         = scol.object_id
       INNER JOIN sys.schemas ssch
               ON ssch.schema_id         = stab.schema_id;

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

반응형

'programing' 카테고리의 다른 글

특정 파일("우리", "내", "그들의")에 대한 Git 병합 전략을 선택합니다.  (0) 2023.05.01
단일 SQL Server 문이 원자적이고 일관성이 있습니까?  (0) 2023.04.26
Azure 가상 시스템이 웹 사이트를 지원하도록 할 수 없습니다.  (0) 2023.04.26
Azure 웹 사이트가 절전 모드로 전환되는 것을 방지하는 방법은 무엇입니까?  (0) 2023.04.26
SQL Server 커서에서 여러 값 가져오기  (0) 2023.04.26

'programing'의 다른글

  • 현재글INFORMATION_SCHEMA를 사용하여 기본 제약 조건을 찾으려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바