--
创建包含所有要建立表及字段的表 myT
use Northwind
go
if object_id( ' myT ' ) is not null
drop table myT
go
create table myT(
TN VARCHAR( 20 ),
TC VARCHAR( 10 ),
CT VARCHAR( 20 ),
DEF INT
)
INSERT INTO myT values(
' abc ' , ' c1 ' , ' int ' , 1
)
INSERT INTO myT values(
' abc ' , ' c2 ' , ' varchar(200) ' , 0
)
INSERT INTO myT values(
' def ' , ' c3 ' , ' xml ' , 1
)
INSERT INTO myT values(
' def ' , ' c4 ' , ' varchar(100) ' , 0
)
go
-- select * from myT
-- 创建所有表
declare @total int
declare @count int
declare @sql varchar( 1000 )
declare @name varchar( 200 )
set @count = 0
set @sql = ''
select @total = count(distinct tn) from myT -- 计算表的个数
while @count < @total -- 循环遍历所有的表名
begin
with tmp1 as (select distinct tn from myT),
tmp2 as (select tn,Row_Number() over(order by tn) as rownum from tmp1)
select @name = tn from tmp2
where rownum = @count + 1
-- 创建各个表
select @sql = @sql + ' ' + tc + ' ' + ct + ' ' + case when def = 1 then ' not null '
when def = 0 then ' null '
end + ' , '
from myT where tn = @name
set @sql = ' create table ' + @name + ' ( ' + @sql + ' ) '
select @sql
set @count = @count + 1
end
/**/ /*
--创建所有表
declare @total int
declare @count int
declare @distable varchar(50)
declare @givename varchar(100)
declare @sql varchar(1000)
declare @name varchar(200)
set @count=0
set @distable=''
--计算表的个数
select @total=count(distinct tn) from myT
--select @total
while @count<@total -- 循环遍历所有的表名
begin
with test as(
select distinct tn from myT
),
test2 as(
select tn,Row_Number() over(order by tn) as rownum from test
)
select @distable=tn from test2
where rownum =@count+1
-- 创建各个表
set @givename=@distable
select @ext=count(1) from myT where tn=@givename
if(@ext=0)
begin
print('Error')
end
else
begin
set @sql=''
set @name =''
set @name=@givename
select @sql=@sql+' '+tc+' '+ct+' '+case when def=1 then 'not null'
when def=0 then 'null'
end+','
from myT where tn=@givename
set @sql ='create table '+@name+'('+@sql+')'
select @sql
--exec (@sql)
end
set @count=@count+1
end
*/
use Northwind
go
if object_id( ' myT ' ) is not null
drop table myT
go
create table myT(
TN VARCHAR( 20 ),
TC VARCHAR( 10 ),
CT VARCHAR( 20 ),
DEF INT
)
INSERT INTO myT values(
' abc ' , ' c1 ' , ' int ' , 1
)
INSERT INTO myT values(
' abc ' , ' c2 ' , ' varchar(200) ' , 0
)
INSERT INTO myT values(
' def ' , ' c3 ' , ' xml ' , 1
)
INSERT INTO myT values(
' def ' , ' c4 ' , ' varchar(100) ' , 0
)
go
-- select * from myT
-- 创建所有表
declare @total int
declare @count int
declare @sql varchar( 1000 )
declare @name varchar( 200 )
set @count = 0
set @sql = ''
select @total = count(distinct tn) from myT -- 计算表的个数
while @count < @total -- 循环遍历所有的表名
begin
with tmp1 as (select distinct tn from myT),
tmp2 as (select tn,Row_Number() over(order by tn) as rownum from tmp1)
select @name = tn from tmp2
where rownum = @count + 1
-- 创建各个表
select @sql = @sql + ' ' + tc + ' ' + ct + ' ' + case when def = 1 then ' not null '
when def = 0 then ' null '
end + ' , '
from myT where tn = @name
set @sql = ' create table ' + @name + ' ( ' + @sql + ' ) '
select @sql
set @count = @count + 1
end
/**/ /*
--创建所有表
declare @total int
declare @count int
declare @distable varchar(50)
declare @givename varchar(100)
declare @sql varchar(1000)
declare @name varchar(200)
set @count=0
set @distable=''
--计算表的个数
select @total=count(distinct tn) from myT
--select @total
while @count<@total -- 循环遍历所有的表名
begin
with test as(
select distinct tn from myT
),
test2 as(
select tn,Row_Number() over(order by tn) as rownum from test
)
select @distable=tn from test2
where rownum =@count+1
-- 创建各个表
set @givename=@distable
select @ext=count(1) from myT where tn=@givename
if(@ext=0)
begin
print('Error')
end
else
begin
set @sql=''
set @name =''
set @name=@givename
select @sql=@sql+' '+tc+' '+ct+' '+case when def=1 then 'not null'
when def=0 then 'null'
end+','
from myT where tn=@givename
set @sql ='create table '+@name+'('+@sql+')'
select @sql
--exec (@sql)
end
set @count=@count+1
end
*/