Создание расширеных свойств на таблице 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