All infos - Создание расширеных свойств на таблице sql server

Создание расширеных свойств на таблице sql server

Пример создания расширенных свойств для таблицы на sql server.

USE [DB_NAME];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[ProductFragmentationHistory]
        ([Id]                  [INT] IDENTITY(1, 1) NOT NULL, 
         [ProductFragmentationId] [INT] NOT NULL, 
         [ProductId]              INT NOT NULL, 
         [ParentId]            INT NOT NULL, 
         [Created]             DATETIME NOT NULL, 
         [UserId]              INT NULL, 
         [Archived]            [DATETIME] NOT NULL, 
         CONSTRAINT [PK_ProductFragmentationHISTORY] PRIMARY KEY CLUSTERED([Id] ASC)
        )

GO

IF NOT EXISTS
(
    SELECT NULL
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[ProductFragmentationHistory]')
          AND type IN(N'U')
)
    BEGIN
        CREATE TABLE [dbo].[ProductFragmentationHistory]
        ([Id]                  [INT] IDENTITY(1, 1) NOT NULL, 
         [ProductFragmentationId] [INT] NOT NULL, 
         [ProductId]              INT NOT NULL, 
         [ParentId]            INT NOT NULL, 
         [Created]             DATETIME NOT NULL, 
         [UserId]              INT NULL, 
         [Archived]            [DATETIME] NOT NULL, 
         CONSTRAINT [PK_ProductFragmentationHISTORY] PRIMARY KEY CLUSTERED([Id] ASC)
        )
END;
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM sys.all_columns c
         JOIN sys.tables t ON t.object_id = c.object_id
         JOIN sys.schemas s ON s.schema_id = t.schema_id
         JOIN sys.default_constraints d ON c.default_object_id = d.object_id
    WHERE t.name = 'ProductFragmentationHistory'
          AND c.name = 'Archived'
          AND s.name = 'dbo'
)
    BEGIN
        ALTER TABLE [dbo].[ProductFragmentationHistory]
        ADD CONSTRAINT [DF_ProductFRAGMENTATIONHISTORY_ARCHIVED] DEFAULT(GETDATE()) FOR [Archived];
END;
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] =
    (
        SELECT [column_id]
        FROM SYS.COLUMNS
        WHERE [name] = 'Id'
              AND [object_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
    )
)
    EXECUTE sys.sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Уникальный идентификатор', 
            @level0type = N'SCHEMA', 
            @level0name = 'dbo', 
            @level1type = N'TABLE', 
            @level1name = 'ProductFragmentationHistory', 
            @level2type = N'COLUMN', 
            @level2name = N'Id';
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] =
    (
        SELECT [column_id]
        FROM SYS.COLUMNS
        WHERE [name] = 'ProductFragmentationId'
              AND [object_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
    )
)
    EXECUTE sys.sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Уникальный идентификатор связи фрагментированного Product ', 
            @level0type = N'SCHEMA', 
            @level0name = 'dbo', 
            @level1type = N'TABLE', 
            @level1name = 'ProductFragmentationHistory', 
            @level2type = N'COLUMN', 
            @level2name = N'ProductFragmentationId';
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] =
    (
        SELECT [column_id]
        FROM SYS.COLUMNS
        WHERE [name] = 'ProductId'
              AND [object_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
    )
)
    EXECUTE sys.sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Идентификатор фрагментированного Product', 
            @level0type = N'SCHEMA', 
            @level0name = 'dbo', 
            @level1type = N'TABLE', 
            @level1name = 'ProductFragmentationHistory', 
            @level2type = N'COLUMN', 
            @level2name = N'ProductId';
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] =
    (
        SELECT [column_id]
        FROM SYS.COLUMNS
        WHERE [name] = 'ParentID'
              AND [object_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
    )
)
    EXECUTE sys.sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Идентификатор родителя фрагментированного Product', 
            @level0type = N'SCHEMA', 
            @level0name = 'dbo', 
            @level1type = N'TABLE', 
            @level1name = 'ProductFragmentationHistory', 
            @level2type = N'COLUMN', 
            @level2name = N'ParentId';
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] =
    (
        SELECT [column_id]
        FROM SYS.COLUMNS
        WHERE [name] = 'Created'
              AND [object_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
    )
)
    EXECUTE sys.sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Дата создания фрагментированного Product', 
            @level0type = N'SCHEMA', 
            @level0name = 'dbo', 
            @level1type = N'TABLE', 
            @level1name = 'ProductFragmentationHistory', 
            @level2type = N'COLUMN', 
            @level2name = N'Created';
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] =
    (
        SELECT [column_id]
        FROM SYS.COLUMNS
        WHERE [name] = 'UserId'
              AND [object_id] = OBJECT_ID('dbo.ProductFragmentationHistory')
    )
)
    EXECUTE sys.sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Идентификатор пользователя создавшего фрагментацию Product', 
            @level0type = N'SCHEMA', 
            @level0name = 'dbo', 
            @level1type = N'TABLE', 
            @level1name = 'ProductFragmentationHistory', 
            @level2type = N'COLUMN', 
            @level2name = N'UserID';
GO
IF NOT EXISTS
(
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('ProductFragmentationHistory')
          AND [name] = N'MS_Description'
          AND [minor_id] = 0
)
    EXEC sys.sp_addextendedproperty 
         @name = N'MS_Description', 
         @value = N'Таблица связей фрагментированных Product', 
         @level0type = N'SCHEMA', 
         @level0name = N'dbo', 
         @level1type = N'TABLE', 
         @level1name = N'ProductFragmentationHistory';
GO

Share this post

82 комментарии

  • sikis izle
    написал sikis izle Комментировать

    We are will rapidly as well as effectively generate a guarantee Premium remodelling manhattan. Lyndsay Chandler Zandra

    Вторник, 08 декабря 2020 17:20
  • Order shipped by
    написал Order shipped by Комментировать

    Ich möchte nur kommentieren, damit Sie verstehen, welche schöne Entdeckung unser Mädchen beim Betrachten des Blogs gemacht hat. Damaris Skipton Bronder

    Суббота, 14 ноября 2020 16:49

Оставить комментарий

Убедитесь, что Вы ввели всю требуемую информацию, в поля, помеченные звёздочкой (*). HTML код не допустим.

Оцените материал
(0 голосов)
Прочитано 559 раз
Другие материалы в этой категории: « Конвертация строки в число