SQL Server 新建表案例语句

GO
IF NOT EXISTS
(
    SELECT 1
    FROM sys.objects o
    WHERE o.name = 'EgSys_ActualFreightSurcharge'
          AND o.type = 'u'
)
BEGIN
    CREATE TABLE [dbo].[EgSys_ActualFreightSurcharge]
    (
        [FreightSurchargeId] [INT] IDENTITY(1, 1) NOT NULL,
        [FreightId] [INT] NOT NULL,
        [USDExchangeRateToCNY] [DECIMAL](13,4) NOT NULL DEFAULT 0,
        [USDExchangeRateToEUR] [DECIMAL](13,4) NOT NULL DEFAULT 0,
        [PeakSeasonSurchargeRate] [DECIMAL](13,4) NOT NULL DEFAULT 0,
        [TariffAmount] [DECIMAL](13,4) NOT NULL DEFAULT 0,
        [FuelSurchargeRate] [DECIMAL](13,4) NOT NULL DEFAULT 0,
        [CreateDate] [DATETIME] NOT NULL,
        [CreateBy] [VARCHAR](50) NOT NULL,
        [UpdateDate] [DATETIME] NOT NULL,
        [UpdateBy] [VARCHAR](50) NOT NULL,
        CONSTRAINT [PK_EgSys_ActualFreightSurcharge]
        PRIMARY KEY CLUSTERED ([FreightSurchargeId] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'主键',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'FreightSurchargeId';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'EgSys_ActualFreightMain表主键',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'FreightId';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'美金兑人民币汇率',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'USDExchangeRateToCNY';
                                    
    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'美金兑欧元汇率',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'USDExchangeRateToEUR';
                                    
    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'旺季附加费费率',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'PeakSeasonSurchargeRate';
                                    
    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'关税金额',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'TariffAmount';
                                    
    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'燃油附加费费率',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'FuelSurchargeRate';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'创建时间',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'CreateDate';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'创建人',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'CreateBy';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'更新时间',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'UpdateDate';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'更新人',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'COLUMN',
                                    @level2name = N'UpdateBy';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'物流附加费配置表',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge';

    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'主键索引',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightSurcharge',
                                    @level2type = N'CONSTRAINT',
                                    @level2name = N'PK_EgSys_ActualFreightSurcharge';

END;

IF NOT EXISTS ( 
    SELECT 1 FROM sys.sysobjects (NOLOCK) so
    INNER JOIN sys.syscolumns (NOLOCK) sc ON so.id = sc.id
    WHERE so.name = 'EgSys_ActualFreightMain' AND sc.name = 'UpdateDate'
)
BEGIN
    ALTER TABLE dbo.EgSys_ActualFreightMain ADD UpdateDate DATETIME NOT NULL DEFAULT GETDATE()
    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'更新时间', 
                                    @level0type = N'SCHEMA', 
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE', 
                                    @level1name = N'EgSys_ActualFreightMain',
                                    @level2type = N'COLUMN', 
                                    @level2name = N'UpdateDate'    
END;

IF NOT EXISTS ( 
    SELECT 1 FROM sys.sysobjects (NOLOCK) so
    INNER JOIN sys.syscolumns (NOLOCK) sc ON so.id = sc.id
    WHERE so.name = 'EgSys_ActualFreightMain' AND sc.name = 'UpdateBy'
)
BEGIN
    ALTER TABLE dbo.EgSys_ActualFreightMain ADD UpdateBy VARCHAR(50) NOT NULL DEFAULT ''
    EXEC sys.sp_addextendedproperty @name = N'MS_Description',
                                    @value = N'更新人',
                                    @level0type = N'SCHEMA',
                                    @level0name = N'dbo',
                                    @level1type = N'TABLE',
                                    @level1name = N'EgSys_ActualFreightMain',
                                    @level2type = N'COLUMN',
                                    @level2name = N'UpdateBy'
END;
GO