--创建包含所有要建立表及字段的表 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 */