号段数据结构——SQLServer数据结构

手机号段扩展版

CREATE TABLE [dbo].[table] (
  [prefix] int  NULL,
  [paragraph] int  NOT NULL,
  [province] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [city_county] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [province_simple] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [city_county_simple] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [isp] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [asc_isp] nvarchar(4) COLLATE Chinese_PRC_CI_AS  NULL,
  [zip_code] nvarchar(6) COLLATE Chinese_PRC_CI_AS  NULL,
  [zone_description] nvarchar(4) COLLATE Chinese_PRC_CI_AS  NULL,
  [province_id] nvarchar(12) COLLATE Chinese_PRC_CI_AS  NULL,
  [city_county_id] nvarchar(12) COLLATE Chinese_PRC_CI_AS  NULL,
  [lng] nvarchar(15) COLLATE Chinese_PRC_CI_AS  NULL,
  [lat] nvarchar(15) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[table] SET (LOCK_ESCALATION = TABLE)
GO

DECLARE @columns TABLE (
    [column_name] NVARCHAR(128),
    [description] NVARCHAR(255)
)

INSERT INTO @columns ([column_name], [description])
VALUES
    ('prefix', '前缀'),
    ('paragraph', '号段'),
    ('province', '省份'),
    ('city_county', '城市'),
    ('province_simple', '省份简称'),
    ('city_county_simple', '城市简称'),
    ('isp', '运营商'),
    ('asc_isp', '虚拟运营商归属ISP'),
    ('zip_code', '邮编'),
    ('zone_description', '区号'),
    ('province_id', '省份行政代码'),
    ('city_county_id', '城市行政代码'),
    ('lng', '经度'),
    ('lat', '纬度')

DECLARE @column_name NVARCHAR(128)
DECLARE @description NVARCHAR(255)

DECLARE column_cursor CURSOR FOR
SELECT [column_name], [description]
FROM @columns

OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @column_name, @description

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_addextendedproperty
    'MS_Description', @description,
    'SCHEMA', 'dbo',
    'TABLE', 'table',
    'COLUMN', @column_name

    FETCH NEXT FROM column_cursor INTO @column_name, @description
END

CLOSE column_cursor
DEALLOCATE column_cursor

手机号段简化版

CREATE TABLE [dbo].[table] (
  [prefix] int  NULL,
  [paragraph] int  NOT NULL,
  [provice_simple] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [city_county_simple] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [isp] nvarchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [zip_code] nvarchar(6) COLLATE Chinese_PRC_CI_AS  NULL,
  [zone_description] nvarchar(4) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[table] SET (LOCK_ESCALATION = TABLE)
GO

DECLARE @columns TABLE (
    [column_name] NVARCHAR(128),
    [description] NVARCHAR(255)
)

INSERT INTO @columns ([column_name], [description])
VALUES
    ('prefix', '前缀'),
    ('paragraph', '号段'),
    ('provice_simple', '省份简称'),
    ('city_county_simple', '城市简称'),
    ('isp', '运营商'),
    ('zip_code', '邮编'),
    ('zone_description', '区号')

DECLARE @column_name NVARCHAR(128)
DECLARE @description NVARCHAR(255)

DECLARE column_cursor CURSOR FOR
SELECT [column_name], [description]
FROM @columns

OPEN column_cursor
FETCH NEXT FROM column_cursor INTO @column_name, @description

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_addextendedproperty
    'MS_Description', @description,
    'SCHEMA', 'dbo',
    'TABLE', 'table',
    'COLUMN', @column_name

    FETCH NEXT FROM column_cursor INTO @column_name, @description
END

CLOSE column_cursor
DEALLOCATE column_cursor