/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 功能：根据提问者提供的原始数据，生成测试数据表。 *
* 说明：1.单个/多个测试表一起生成。 *
* 2.表类型：变量表/临时表/实体表，可控。 *
* 3.如无表名：T1...Tn；如无列名：c1...cn。 *
* 4.列填充：数据列不足填充NULL值；字段列不足以sql_variant填充cm...cn。*
* 5.数据类型解析：自动解析最恰当的数据类型/长度。 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/*
--> 多表测试：
cs '@var
id data
1 表变量
#tmp
id data
2 临时表
$tab
id,data
3 实体表'
go
/*
--> 测试数据: @var
declare @var table (id int,data varchar(6))
insert into @var
select 1,'表变量'
--> 测试数据: #tmp
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp
create table #tmp (id int,data varchar(6))
insert into #tmp
select 2,'临时表'
--> 测试数据: [tab]
if object_id('[tab]') is not null drop table [tab]
create table [tab] (id int,data varchar(6))
insert into [tab]
select 3,'实体表'
select * from @var
select * from #tmp
select * from [tab]
*/
--> 无表名/无列名测试：
cs '@
id data
1 无表名
0#tmp
2 无列名
0$
3 双无'
go
/*
--> 测试数据: @T1
declare @T1 table (id int,data varchar(6))
insert into @T1
select 1,'无表名'
--> 测试数据: #tmp
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp
create table #tmp (c1 int,c2 varchar(6))
insert into #tmp
select 2,'无列名'
--> 测试数据: [T3]
if object_id('[T3]') is not null drop table [T3]
create table [T3] (c1 int,c2 varchar(4))
insert into [T3]
select 3,'双无'
select * from @T1
select * from #tmp
select * from [T3]
*/
--> 保留原NULL值，字段数据分隔线忽略：
cs '@null
a b
---- ----
11 null
null 22'
go
/*
--> 测试数据: @null
declare @null table (a int,b int)
insert into @null
select 11,null union all
select null,22
select * from @null
*/
--> 数据列不足，NULL值填充，数据左边对齐原则：
cs '@fill_null
a b c d
11 12 13
21 22
31'
go
/*
--> 测试数据: @fill_null
declare @fill_null table (a int,b int,c int,d sql_variant)
insert into @fill_null
select 11,12,13,null union all
select 21,22,null,null union all
select 31,null,null,null
select * from @fill_null
*/
--> 字段名不足，动态添加字段，以cm...cn命名：
cs '@add_column
a
11 12 13
21 22
31'
go
/*
--> 测试数据: @add_column
declare @add_column table (a int,c2 int,c3 int)
insert into @add_column
select 11,12,13 union all
select 21,22,null union all
select 31,null,null
select * from @add_column
*/
--> 占位符分号的作用——控制数据的位置，用NULL代替效果一样：
cs '@semicolon
a b c
11 12 13
; 22 23
; ; 33'
go
/*
--> 测试数据: @semicolon
declare @semicolon table (a int,b int,c int)
insert into @semicolon
select 11,12,13 union all
select null,22,23 union all
select null,null,33
select * from @semicolon
*/
-->时间解析一：
cs '@time
id date boy
1 2001-1-1 Mark
2 20020101 John
3 23:15:39 Paul'
go
/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(4))
insert into @time
select 1,'2001-1-1','Mark' union all
select 2,'20020101','John' union all
select 3,'23:15:39','Paul'
select * from @time
*/
-->时间解析二：
cs '@time
id date boy
1 2001-1-1 12:28:47 Mark
2 20020101 17:30:00 John
3 2003/1/1 23:15:39 Paul'
go
/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(4))
insert into @time
select 1,'2001-1-1 12:28:47','Mark' union all
select 2,'20020101 17:30:00','John' union all
select 3,'2003/1/1 23:15:39','Paul'
select * from @time
*/
-->时间解析三——不支持：
cs '@time
id date boy
1 2001-1-1 12:28:47 Mark
2 20020101 John
3 2003/1/1 Paul'
go
/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(8),c4 varchar(4))
insert into @time
select 1,'2001-1-1','12:28:47','Mark' union all
select 2,'20020101','John',null union all
select 3,'2003/1/1','Paul',null
select * from @time
*/
-->时间解析三——正确解析需要手工添加占位符：
cs '@time
id date boy
1 2001-1-1 12:28:47 Mark
2 20020101 ; John
3 2003/1/1 ; Paul'
go
/*
--> 测试数据: @time
declare @time table (id int,date datetime,boy varchar(4))
insert into @time
select 1,'2001-1-1 12:28:47','Mark' union all
select 2,'20020101','John' union all
select 3,'2003/1/1','Paul'
select * from @time
*/
*/
CREATE PROCEDURE cs @input VARCHAR(MAX)
AS
SET nocount ON
IF PATINDEX('%[@#$]%', @input) = 0
RETURN
SELECT @input = REPLACE(@input, ' ', ' '), @input = LTRIM(RTRIM(@input))
SELECT TOP 94
code = IDENTITY( TINYINT,33,1), m = CAST(NULL AS VARCHAR(2)),
w = CAST(NULL AS VARCHAR(1))
INTO #
FROM syscolumns
SELECT @input = REPLACE(@input COLLATE chinese_prc_cs_as_ks_ws,
NCHAR(code + 65248), CHAR(code))
FROM #
TRUNCATE TABLE #
INSERT INTO #
SELECT CHAR(13), CHAR(10)
INSERT INTO #
SELECT CHAR(9), ','
INSERT INTO #
SELECT ' ', ','
INSERT INTO #
SELECT '|', ','
INSERT INTO #
SELECT '¦', ','
INSERT INTO #
SELECT '。', '.'
INSERT INTO #
SELECT '·', '.'
INSERT INTO #
SELECT CHAR(39), ''
SELECT @input = REPLACE(@input COLLATE chinese_prc_cs_as_ks_ws, m, w)
FROM #
DROP TABLE #
WHILE PATINDEX('%[^,][;]%', @input) > 0
SET @input = STUFF(@input, PATINDEX('%[^,][;]%', @input) + 1, 1, ',;')
WHILE PATINDEX('%[;][^,]%', @input) > 0
SET @input = STUFF(@input, PATINDEX('%[;][^,]%', @input), 1, ';,')
SET @input = REPLACE(@input, ';', 'null')
WHILE CHARINDEX(',,', @input) > 0
SET @input = REPLACE(@input, ',,', ',')
SET @input = REPLACE(@input, CHAR(10) + ',', CHAR(10))
SET @input = REPLACE(@input, ',' + CHAR(10), CHAR(10))
WHILE CHARINDEX(CHAR(10) + CHAR(10), @input) > 0
SET @input = REPLACE(@input, CHAR(10) + CHAR(10), CHAR(10))
IF LEFT(@input, 1) = CHAR(10)
SET @input = RIGHT(@input, LEN(@input) - 1)
IF RIGHT(@input, 1) <> CHAR(10)
SET @input = @input + CHAR(10)
DECLARE @tab SYSNAME -- 表名：@=变量表；#=临时表；$=实体表
DECLARE @tid TINYINT
DECLARE @yes BIT
DECLARE @cid SMALLINT
DECLARE @col VARCHAR(1000)
DECLARE @max SMALLINT
DECLARE @type SYSNAME
DECLARE @lenp SMALLINT
DECLARE @lens SMALLINT
DECLARE @sql VARCHAR(8000)
DECLARE @tabs TABLE
(
id INT IDENTITY ,
name SYSNAME
)
DECLARE @data TABLE
(
id INT IDENTITY ,
data VARCHAR(8000)
)
DECLARE @temp TABLE
(
id INT ,
temp VARCHAR(1000)
)
DECLARE @code TABLE
(
id INT ,
code VARCHAR(8000)
)
IF CHARINDEX('$', @input) > 0
BEGIN
SET @tab = SUBSTRING(@input, CHARINDEX('$', @input) + 1,
CHARINDEX(CHAR(10), @input,
CHARINDEX('$', @input))
- CHARINDEX('$', @input) - 1)
IF OBJECT_ID(@tab) IS NOT NULL
BEGIN
RAISERROR(N'数据库中已存在名为 ''%s'' 的对象。',16,1,@tab)
RETURN
END
END
WHILE PATINDEX('%[@#$]%', @input) > 0
BEGIN
SELECT @tab = LEFT(@input, CHARINDEX(CHAR(10), @input) - 1),
@tid = ISNULL(@tid, 0) + 1, @yes = 1, @cid = 1,
@input = RIGHT(@input,
LEN(@input) - CHARINDEX(CHAR(10), @input))
IF LEFT(@tab, 1) = '0'
SELECT @tab = STUFF(@tab, 1, 1, ''), @yes = 0
IF LEN(@tab) = 1
SET @tab = @tab + 'T' + LTRIM(@tid)
IF LEFT(@tab, 1) = '$'
SET @tab = QUOTENAME(STUFF(@tab, 1, 1, ''))
INSERT INTO @tabs
VALUES ( @tab )
IF @yes = 0
SET @col = NULL
ELSE
SELECT @col = LEFT(@input, CHARINDEX(CHAR(10), @input) - 1)
+ ',',
@input = RIGHT(@input,
LEN(@input) - CHARINDEX(CHAR(10),
@input)),
@col = REPLACE(@col, ',', ':')
WHILE CHARINDEX(CHAR(10), @input) > 0
BEGIN
INSERT INTO @data
SELECT LEFT(@input,
CHARINDEX(CHAR(10), @input) - 1)
SET @input = RIGHT(@input,
LEN(@input) - CHARINDEX(CHAR(10),
@input))
IF LEFT(@input, 1) IN ( '@', '#', '$' )
OR LEFT(@input, 2) IN ( '0@', '0#', '0$' )
BREAK
END
DELETE FROM @data
WHERE PATINDEX('%[^,-]%', data) = 0
SELECT @max = MAX(LEN(data) - LEN(REPLACE(data, ',', '')))
FROM @data
UPDATE @data
SET data = data + REPLICATE(',null',
@max - LEN(data)
+ LEN(REPLACE(data, ',', '')))
+ ','
SET @max = ISNULL(LEN(@col) - LEN(REPLACE(@col, ':', '')), 0)
INSERT INTO @code
SELECT id, NULL
FROM @data
ORDER BY id
WHILE EXISTS ( SELECT 1
FROM @data
WHERE CHARINDEX(',', data) > 0 )
BEGIN
INSERT INTO @temp
SELECT id,
NULLIF(LEFT(data, CHARINDEX(',', data) - 1),
'null')
FROM @data
ORDER BY id
UPDATE @data
SET data = RIGHT(data,
LEN(data) - CHARINDEX(',', data))
IF EXISTS ( SELECT 1
FROM @temp a
INNER JOIN @data b ON a.id = b.id
AND a.temp IS NOT NULL
AND LEFT(b.data,
4) NOT IN ( '',
'null' ) )
AND NOT EXISTS ( SELECT 1
FROM @temp a
INNER JOIN @data b ON a.id = b.id
AND a.temp IS NOT NULL
AND LEFT(b.data,
4) NOT IN ( '',
'null' )
AND ISDATE(a.temp
+ SPACE(1)
+ LEFT(b.data,
CHARINDEX(',',
b.data) - 1)) = 0 )
BEGIN
UPDATE a
SET a.temp = a.temp + SPACE(1) + LEFT(b.data,
CHARINDEX(',',
b.data) - 1)
FROM @temp a
INNER JOIN @data b ON a.id = b.id
WHERE a.temp IS NOT NULL
AND LEFT(b.data,
CHARINDEX(',', b.data) - 1) <> 'null'
UPDATE @data
SET data = RIGHT(data,
LEN(data) - CHARINDEX(',',
data))
END
IF NOT EXISTS ( SELECT 1
FROM @temp
WHERE temp IS NOT NULL )
SET @type = ' sql_variant'
ELSE
IF NOT EXISTS ( SELECT 1
FROM @temp
WHERE temp IS NOT NULL
AND ISNUMERIC(temp) = 0 )
BEGIN
IF EXISTS ( SELECT 1
FROM @temp
WHERE PATINDEX('%[Ee]%', temp) > 0 )
SET @type = ' float'
ELSE
IF EXISTS ( SELECT 1
FROM @temp
WHERE CHARINDEX('.', temp) > 0 )
BEGIN
SELECT @lenp = MAX(CHARINDEX('.',
CASE
WHEN LEFT(temp,
1) = '-'
THEN RIGHT(temp,
LEN(temp) - 1)
ELSE temp
END)) - 1,
@lens = MAX(CHARINDEX('.',
REVERSE(temp)))
- 1
FROM @temp
WHERE CHARINDEX('.', temp) > 0
IF @lenp + @lens > @@max_precision
SET @type = ' float'
ELSE
SET @type = ' numeric('
+ LTRIM(@lenp + @lens)
+ ',' + LTRIM(@lens) + ')'
END
ELSE
IF EXISTS ( SELECT 1
FROM @temp
WHERE LEN(temp) > 1
AND LEFT(temp, 1) = '0' )
SELECT @type = ' varchar('
+ LTRIM(MAX(DATALENGTH(temp)))
+ ')'
FROM @temp
WHERE temp IS NOT NULL
ELSE
IF EXISTS ( SELECT
1
FROM @temp
WHERE temp IS NOT NULL
AND LEN(temp) <> 8
OR ISDATE(temp) = 0 )
BEGIN
SELECT @lenp = ISNULL(MAX(LEN(temp)),
0)
FROM @temp
WHERE LEFT(temp, 1) <> '-'
SELECT @lens = -ISNULL(MAX(LEN(temp)
- 1), 0)
FROM @temp
WHERE LEFT(temp, 1) = '-'
IF @lenp <= ABS(@lens)
SELECT
@lenp = @lenp
+ @lens,
@lens = @lenp
- @lens,
@lenp = @lenp
- @lens
IF ABS(@lenp) > 38
SET @type = ' varchar('
+ LTRIM(CASE
WHEN @lenp > 0
THEN @lenp
ELSE ABS(@lenp)
+ 1
END) + ')'
ELSE
IF EXISTS ( SELECT
1
FROM
@temp
WHERE
CAST(temp AS NUMERIC(38,
0)) NOT BETWEEN -9223372036854775808
AND
9223372036854775807 )
SET @type = ' numeric('
+ LTRIM(ABS(@lenp))
+ ',0)'
ELSE
IF EXISTS ( SELECT
1
FROM
@temp
WHERE
CAST(temp AS NUMERIC(38,
0)) NOT BETWEEN -2147483648
AND
2147483647 )
SET @type = ' bigint'
ELSE
SET @type = ' int'
END
ELSE
SET @type = ' datetime'
END
ELSE
IF NOT EXISTS ( SELECT 1
FROM @temp
WHERE temp IS NOT NULL
AND ISDATE(temp) = 0 )
SET @type = ' datetime'
ELSE
SELECT @type = ' varchar('
+ LTRIM(MAX(DATALENGTH(temp))) + ')'
FROM @temp
WHERE temp IS NOT NULL
IF CHARINDEX(':', @col) > 0
SET @col = STUFF(@col, CHARINDEX(':', @col), 1,
@type + CASE WHEN @cid = @max THEN ''
ELSE ','
END)
ELSE
SET @col = ISNULL(@col + ',', '') + 'c' + LTRIM(@cid)
+ @type
UPDATE a
SET a.code = ISNULL(a.code + ',', 'select ')
+ CASE WHEN SUBSTRING(@type, 2, 3) IN ( 'flo',
'num', 'big',
'int' )
THEN ISNULL(b.temp, 'null')
ELSE ISNULL(QUOTENAME(b.temp, CHAR(39)),
'null')
END
FROM @code a
INNER JOIN @temp b ON a.id = b.id
DELETE FROM @temp
SET @cid = @cid + 1
END
IF @max > @cid - 1
BEGIN
SELECT @col = STUFF(@col, LEN(@col), 1, ' sql_variant'),
@col = REPLACE(@col, ':', ' sql_variant,')
UPDATE @code
SET code = code + REPLICATE(',null', @max - @cid + 1)
END
SET @sql = NULL
SELECT @sql = ISNULL(@sql + ' union all' + CHAR(13) + CHAR(10),
'') + code
FROM @code
ORDER BY id
PRINT '--> 测试数据: ' + @tab
PRINT CASE LEFT(@tab, 1)
WHEN '@' THEN 'declare ' + @tab + ' table'
WHEN '#'
THEN 'if object_id(''tempdb.dbo.' + @tab
+ ''') is not null drop table ' + @tab + CHAR(13)
+ CHAR(10) + 'create table ' + @tab
ELSE 'if object_id(''' + @tab
+ ''') is not null drop table ' + @tab + CHAR(13)
+ CHAR(10) + 'create table ' + @tab
END + ' (' + @col + ')'
PRINT 'insert into ' + @tab
PRINT @sql
DELETE FROM @data
DELETE FROM @code
END
SET @sql = NULL
SELECT @sql = ISNULL(@sql + CHAR(13) + CHAR(10), CHAR(13) + CHAR(10))
+ 'select * from ' + name
FROM @tabs
ORDER BY id
PRINT @sql
SET nocount OFF
go