IPv4扩展版
CREATE TABLE [dbo].[table] (
[ip_start] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[ip_end] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[num_start] bigint NULL,
[num_end] bigint NULL,
[num] bigint NULL,
[land] nvarchar(4) COLLATE Chinese_PRC_CI_AS NULL,
[country] nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[region] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[city] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[area] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[land_english] nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[country_english] nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[region_english] nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[city_english] nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[area_english] nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[isp] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[scene] nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[asn] nvarchar(150) COLLATE Chinese_PRC_CI_AS NULL,
[country_id_2] nvarchar(2) COLLATE Chinese_PRC_CI_AS NULL,
[country_id_3] nvarchar(3) COLLATE Chinese_PRC_CI_AS NULL,
[international_code] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[currency] nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[time_zone] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[utc] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[region_id] nvarchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[city_id] nvarchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[area_id] nvarchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[lat] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[lng] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL
)
GO
DECLARE @columns TABLE (
[column_name] NVARCHAR(128),
[description] NVARCHAR(255)
)
INSERT INTO @columns ([column_name], [description])
VALUES
('ip_start', '起始IP'),
('ip_end', '结束IP'),
('num_start', '数字起始IP'),
('num_end', '数字结束IP'),
('num', 'IP数量'),
('land', '大州'),
('country', '国家'),
('region', '省份'),
('city', '城市'),
('area', '区县'),
('land_english', '大州英文'),
('country_english', '国家英文'),
('region_english', '省份英文'),
('city_english', '城市英文'),
('area_english', '区县英文'),
('isp', '运营商'),
('scene', '场景'),
('asn', 'ASN编号'),
('country_id_2', '国家二位码'),
('country_id_3', '国家三位码'),
('international_code', '国际区号'),
('currency', '主要货币'),
('time_zone', '时区标识符'),
('utc', 'UTC偏移量'),
('region_id', '国内一级行政代码'),
('city_id', '国内二级行政代码'),
('area_id', '国内三级行政代码'),
('lat', '经度'),
('lng', '纬度')
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
IPv4基础版
CREATE TABLE [dbo].[table] (
[ip_start] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[ip_end] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[num_start] bigint NULL,
[num_end] bigint NULL,
[num] bigint NULL,
[land] nvarchar(4) COLLATE Chinese_PRC_CI_AS NULL,
[region] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[city] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[area] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[country] nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[country_english] nvarchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[isp] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[country_id] nvarchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[international_code] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[region_id] nvarchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[city_id] nvarchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[area_id] nvarchar(6) COLLATE Chinese_PRC_CI_AS NULL,
[lat] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[lng] 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
('ip_start', '起始IP'),
('ip_end', '结束IP'),
('num_start', '数字起始IP'),
('num_end', '数字结束IP'),
('num', 'IP数量'),
('land', '大州'),
('region', '省份'),
('city', '城市'),
('area', '区县'),
('country', '国家'),
('country_english', '国家英文'),
('isp', '运营商'),
('country_id', '国家代码'),
('international_code', '国际区号'),
('region_id', '一级行政代码'),
('city_id', '二级行政代码'),
('area_id', '三级行政代码'),
('lat', '经度'),
('lng', '纬度')
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
IPv4简化版
CREATE TABLE [dbo].[table] (
[ip_start] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[ip_end] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[num_start] bigint NULL,
[num_end] bigint NULL,
[num] bigint NULL,
[region] nvarchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[country] nvarchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[isp] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[international_code] nvarchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[lat] nvarchar(15) COLLATE Chinese_PRC_CI_AS NULL,
[lng] 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
('ip_start', '起始IP'),
('ip_end', '结束IP'),
('num_start', '数字起始IP'),
('num_end', '数字结束IP'),
('num', 'IP数量'),
('region', '省份'),
('country', '国家'),
('isp', '运营商'),
('international_code', '国际区号'),
('lat', '经度'),
('lng', '纬度')
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