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 комментарии

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

    Utterly pent articles , regards for entropy. Philippe Mackenzie Lareena

    Четверг, 28 января 2021 17:19
  • erotik
    написал erotik Комментировать

    Thanks-a-mundo for the blog post. Really looking forward to read more. Want more. Serena Dennison Dole

    Четверг, 28 января 2021 10:30
  • anime
    написал anime Комментировать

    Although sites we backlink to below are considerably not related to ours, we feel they may be truly really worth a go by, so possess a look. Ingaborg Rod Waddle

    Четверг, 28 января 2021 10:21
  • yabanci dizi
    написал yabanci dizi Комментировать

    Thank you for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your next write ups thank you once again. Lani Garald Shina

    Четверг, 28 января 2021 08:10
  • erotik
    написал erotik Комментировать

    You completed several fine points there. I did a search on the topic and found most persons will have the same opinion with your blog. Hedvige Arty Angy

    Четверг, 28 января 2021 08:01
  • dizi izle
    написал dizi izle Комментировать

    Appreciate it for helping out, excellent information. Katrina Eward Leschen

    Среда, 27 января 2021 22:48
  • dizi
    написал dizi Комментировать

    I think the admin of this web page is in fact working hard in favor of his web site, since here every stuff is quality based material. Gussie Benedict Rudin

    Среда, 27 января 2021 22:38
  • anime
    написал anime Комментировать

    Hi there! I know this is somewhat off-topic but I needed to ask. Yoko Stillman Juan

    Среда, 27 января 2021 21:23
  • turkce
    написал turkce Комментировать

    Hi there, its pleasant article regarding media print, we all understand media is a impressive source of data. Dorry Tam Dorise

    Среда, 27 января 2021 21:12
  • bedava
    написал bedava Комментировать

    Excellent article! We will be linking to this great article on our site. Keep up the great writing. Carla Zacharias Peppy

    Среда, 27 января 2021 14:20

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

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

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