declare @s varchar(8000)declare @table_name varchar(500) declare @update varchar(500) declare @field1 varchar(500) declare @field1_type varchar(500) declare @field2 varchar(500) declare @field2_type varchar(500)DECLARE cur CURSOR FOR SELECT table_name,[update], field1,field1_type, field2,field2_type from tbl_tablefileds WHERE [update]= true OPEN cur FETCH cur INTO @table_name,@update, @field1,@field1_type, @field2,@field2_typeWHILE @@FETCH_STATUS = 0 BEGIN set @s='' SELECT @s =@s + 'create table '+@table_name+'('+@field1+' '+@field1_type+' ,'+@field2+' '+@field2_type+') ' exec(@s) FETCH cur INTO @table_name,@update, @field1,@field1_type, @field2,@field2_type END CLOSE cur DEALLOCATE cur 思路,判断表是否存在等项自己加上吧.
Create table TC (table_id INT ,table_name NVARCHAR(10), [update] NVARCHAR(10),fieldName NVARCHAR(10),field_type NVARCHAR(20))INSERT INTO TC SELECT 1,'TE','TRUE','A1','int' INSERT INTO TC SELECT 1,'TE','TRUE','A2','NVARCHAR(10)' INSERT INTO TC SELECT 1,'TE','TRUE','A3','NVARCHAR(10)' INSERT INTO TC SELECT 1,'TE','TRUE','A4','numeric (10,4)' INSERT INTO TC SELECT 1,'TE','TRUE','A5','int'INSERT INTO TC SELECT 2,'Tb','TRUE','b1','int' goDECLARE @SQL nvarchar(4000) set @SQL='' select @SQL=@SQL+' if exists (select * from dbo.sysobjects where id = object_id(N'''+table_name+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+table_name +char(13)+char(10) from (select distinct table_name from tc where [update]='true' ) a print @sql exec(@sql)declare @a nvarchar(10) declare cat cursor for select distinct table_name from tc where [update]='true' and isnull(fieldName,'')<>'' open cat fetch next from cat into @a while @@fetch_status =0 begin set @SQL='Create table ' + @a+'(' select @SQL= @SQL+' '+fieldname+' '+field_type + ', ' from tc where table_name=@a set @SQL=stuff(@sql,len(@sql),1,')') print @sql exec(@sql) fetch next from cat into @a end close cat deallocate cat
我认为最好加一个主键 ,来标识 是不是 主键drop table tc go Create table TC (table_id INT ,table_name NVARCHAR(10), [update] NVARCHAR(10),fieldName NVARCHAR(10),field_type NVARCHAR(20),PKey bit )INSERT INTO TC SELECT 1,'TE','TRUE','A1','int',1 INSERT INTO TC SELECT 1,'TE','TRUE','A2','NVARCHAR(10)',0 INSERT INTO TC SELECT 1,'TE','TRUE','A3','NVARCHAR(10)',0 INSERT INTO TC SELECT 1,'TE','TRUE','A4','numeric (10,4)',0 INSERT INTO TC SELECT 1,'TE','TRUE','A5','int',0INSERT INTO TC SELECT 2,'Tb','TRUE','b1','int',0 goDECLARE @SQL nvarchar(4000) set @SQL='' select @SQL=@SQL+' if exists (select * from dbo.sysobjects where id = object_id(N'''+table_name+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+table_name +char(13)+char(10) from (select distinct table_name from tc where [update]='true' ) a print @sql exec(@sql)declare @a nvarchar(10) declare cat cursor for select distinct table_name from tc where [update]='true' and isnull(fieldName,'')<>'' open cat fetch next from cat into @a while @@fetch_status =0 begin set @SQL='Create table [' + @a+'] (' select @SQL= @SQL+' ['+fieldname+'] '+field_type + ' PRIMARY KEY CLUSTERED , ' from tc where table_name=@a and PKey=1 select @SQL= @SQL+' ['+fieldname+'] '+field_type + ', ' from tc where table_name=@a and PKey<>1 set @SQL=stuff(@sql,len(@sql),1,')') print @sql exec(@sql) fetch next from cat into @a end close cat deallocate cat
INSERT INTO TC SELECT 1,'TE','TRUE','A1','A2','A3','A4'
INSERT INTO TC SELECT 2,'Tb','TRUE','b1','b2','b3','b4'goDECLARE @SQL nvarchar(4000)
set @SQL=''
select @SQL=@SQL+'
if exists (select * from dbo.sysobjects where id = object_id(N'''+table_name+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+table_name +char(13)+char(10)
from tc
where [update]='true'
--print @sql
exec(@sql)set @SQL=''
select @SQL=@SQL+'Create table ' + table_name+ '( '+field1+' NVARCHAR(10),' +field2 +' NVARCHAR(10),'+field3 +' NVARCHAR(10),'+field4 +' NVARCHAR(10))'+char(13)+char(10)
from tc
where [update]='true'
print @sql
table_id, table_name,update, FieldName,Field_Type
这样如果一个表有几个字段就存成几行,这样判断一个表有几个字段很容易,因为每个表的字段都不是一样的
declare @update varchar(500)
declare @field1 varchar(500)
declare @field1_type varchar(500)
declare @field2 varchar(500)
declare @field2_type varchar(500)DECLARE cur CURSOR FOR
SELECT table_name,[update], field1,field1_type, field2,field2_type
from tbl_tablefileds
WHERE [update]= true
OPEN cur
FETCH cur INTO @table_name,@update, @field1,@field1_type, @field2,@field2_typeWHILE @@FETCH_STATUS = 0
BEGIN
set @s=''
SELECT @s =@s + 'create table '+@table_name+'('+@field1+' '+@field1_type+' ,'+@field2+' '+@field2_type+') '
exec(@s)
FETCH cur INTO @table_name,@update, @field1,@field1_type, @field2,@field2_type
END
CLOSE cur
DEALLOCATE cur
思路,判断表是否存在等项自己加上吧.
Create table TC (table_id INT ,table_name NVARCHAR(10), [update] NVARCHAR(10),fieldName NVARCHAR(10),field_type NVARCHAR(20))INSERT INTO TC SELECT 1,'TE','TRUE','A1','int'
INSERT INTO TC SELECT 1,'TE','TRUE','A2','NVARCHAR(10)'
INSERT INTO TC SELECT 1,'TE','TRUE','A3','NVARCHAR(10)'
INSERT INTO TC SELECT 1,'TE','TRUE','A4','numeric (10,4)'
INSERT INTO TC SELECT 1,'TE','TRUE','A5','int'INSERT INTO TC SELECT 2,'Tb','TRUE','b1','int'
goDECLARE @SQL nvarchar(4000)
set @SQL=''
select @SQL=@SQL+'
if exists (select * from dbo.sysobjects where id = object_id(N'''+table_name+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+table_name +char(13)+char(10)
from (select distinct table_name from tc where [update]='true' ) a
print @sql
exec(@sql)declare @a nvarchar(10)
declare cat cursor for select distinct table_name from tc where [update]='true' and isnull(fieldName,'')<>''
open cat
fetch next from cat into @a
while @@fetch_status =0
begin
set @SQL='Create table ' + @a+'('
select @SQL= @SQL+' '+fieldname+' '+field_type + ', '
from tc where table_name=@a
set @SQL=stuff(@sql,len(@sql),1,')')
print @sql
exec(@sql)
fetch next from cat into @a
end
close cat
deallocate cat
go
Create table TC (table_id INT ,table_name NVARCHAR(10), [update] NVARCHAR(10),fieldName NVARCHAR(10),field_type NVARCHAR(20),PKey bit )INSERT INTO TC SELECT 1,'TE','TRUE','A1','int',1
INSERT INTO TC SELECT 1,'TE','TRUE','A2','NVARCHAR(10)',0
INSERT INTO TC SELECT 1,'TE','TRUE','A3','NVARCHAR(10)',0
INSERT INTO TC SELECT 1,'TE','TRUE','A4','numeric (10,4)',0
INSERT INTO TC SELECT 1,'TE','TRUE','A5','int',0INSERT INTO TC SELECT 2,'Tb','TRUE','b1','int',0
goDECLARE @SQL nvarchar(4000)
set @SQL=''
select @SQL=@SQL+'
if exists (select * from dbo.sysobjects where id = object_id(N'''+table_name+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table '+table_name +char(13)+char(10)
from (select distinct table_name from tc where [update]='true' ) a
print @sql
exec(@sql)declare @a nvarchar(10)
declare cat cursor for select distinct table_name from tc where [update]='true' and isnull(fieldName,'')<>''
open cat
fetch next from cat into @a
while @@fetch_status =0
begin
set @SQL='Create table [' + @a+'] ('
select @SQL= @SQL+' ['+fieldname+'] '+field_type + ' PRIMARY KEY CLUSTERED , ' from tc where table_name=@a and PKey=1
select @SQL= @SQL+' ['+fieldname+'] '+field_type + ', ' from tc where table_name=@a and PKey<>1
set @SQL=stuff(@sql,len(@sql),1,')')
print @sql
exec(@sql)
fetch next from cat into @a
end
close cat
deallocate cat