建表都有哪些字段,插入都插入什么值啊?楼主没说create proc sp_tool(@tbname varchar(100)) as declare @sql varchar(1000) if not exists(select 1 from sysobjects where xtype='U' and name=@tbname) begin set @sql='create table '+@tbname+'(id int identity(1,1))' exec(@sql) endexec sp_tool 'ta'
create procedure sp_tool ta varchar(1000) as if exists(select 1 from sysobjects where name='ta' and xtype='U') insert ta values(col1,col2,col3.....) else print '没有ta表'
create proc test(@name sysname) as begin if exists(select 1 from sysobjects where name=@name and xtype='U') exec('insert '+@name' select * from tb'+')'--有把tb表数据导入变量表 else print '没有此表' return end
create proc test(@name sysname) as if exists(select 1 from sysobjects where name=@name and xtype='U') exec('insert '+@name+' select * from tb')--有把tb表数据导入变量表 else print '没有此表' return
字段要自己写SQL语句抓出来。怎么写啊?
--把它改成存储过程,不知道是否符合你的要求,呵呵 declare @TA varchar(30) set @TA='a'if exists(select 1 from sysobjects where name=@TA and xtype='U') begin declare @s varchar(4000) declare @s1 varchar(2000) declare @s2 varchar(2000) set @s='' set @s1='' set @s2='' select @s2=@s2+'@'+ sc.name +',', @s1=@s1+'@'+ sc.name +' '+(case when st.name like '%char%' then st.name + '('+ cast(sc.length as varchar) + ')' when st.name like '%decimal%' then st.name +'('+ cast(sc.xprec as varchar) + ',' + cast(sc.xscale as varchar) + ')' else st.name end)+',' from dbo.syscolumns sc join dbo.sysobjects so on sc.id=so.id and so.name=@TA join dbo.systypes st on st.xtype=sc.xtype and st.xusertype=sc.xusertype where sc.status & 128=0 --128:标识列 if @s1<>'' set @s1=left(@s1,len(@s1)-1) if @s2<>'' set @s2=left(@s2,len(@s2)-1) set @s='Create Proc Insert_'+ @TA + ' '+@s1+' AS '+'insert into '+ @TA + ' values('+@s2+')' exec(@s) end else print '没有'+@TA+'表'
as
declare @sql varchar(1000)
if not exists(select 1 from sysobjects where xtype='U' and name=@tbname)
begin
set @sql='create table '+@tbname+'(id int identity(1,1))'
exec(@sql)
endexec sp_tool 'ta'
as
if exists(select 1 from sysobjects where name='ta' and xtype='U')
insert ta values(col1,col2,col3.....)
else
print '没有ta表'
create proc test(@name sysname)
as
begin
if exists(select 1 from sysobjects where name=@name and xtype='U')
exec('insert '+@name' select * from tb'+')'--有把tb表数据导入变量表
else
print '没有此表'
return
end
create proc test(@name sysname)
as
if exists(select 1 from sysobjects where name=@name and xtype='U')
exec('insert '+@name+' select * from tb')--有把tb表数据导入变量表
else
print '没有此表'
return
declare @TA varchar(30)
set @TA='a'if exists(select 1 from sysobjects where name=@TA and xtype='U')
begin
declare @s varchar(4000)
declare @s1 varchar(2000)
declare @s2 varchar(2000)
set @s=''
set @s1=''
set @s2='' select @s2=@s2+'@'+ sc.name +',',
@s1=@s1+'@'+ sc.name +' '+(case when st.name like '%char%' then st.name + '('+ cast(sc.length as varchar) + ')'
when st.name like '%decimal%' then st.name +'('+ cast(sc.xprec as varchar) + ',' + cast(sc.xscale as varchar) + ')'
else st.name end)+','
from dbo.syscolumns sc
join dbo.sysobjects so on sc.id=so.id and so.name=@TA
join dbo.systypes st on st.xtype=sc.xtype and st.xusertype=sc.xusertype
where sc.status & 128=0 --128:标识列 if @s1<>'' set @s1=left(@s1,len(@s1)-1)
if @s2<>'' set @s2=left(@s2,len(@s2)-1) set @s='Create Proc Insert_'+ @TA + ' '+@s1+' AS
'+'insert into '+ @TA + ' values('+@s2+')'
exec(@s)
end
else print '没有'+@TA+'表'