declare @TableName varchar(20)

set @TableName = 'TStock'

print '--删除原表'

print 'if exists (select 1 from dbo.sysobjects where id = object_id(N''' + @TableName +''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
drop table ' + @TableName

print ''

print 'GO'

print ''

declare @TableCreate varchar(80)

print '--创建新表'

print 'Create Table ' + @TableName + '('

declare @ColDEF varchar(50)

DECLARE mycursor1
cursor FOR SELECT a.name + ' ' + 
case
when b.name in ('int','bigint') then b.name
when b.name in ('char','varchar') then b.name + ' (' + cast(a.length as varchar(10)) + ')'
when b.name in ('numeric') then b.name + ' (' + cast(a.xprec as varchar(10)) + ',' + cast(a.xscale as varchar(10)) + ')'
end
FROM syscolumns a
left join systypes b
on a.xtype = b.xusertype   
inner join sysobjects d     
on a.id = d.id
where d.name = @TableName
order by a.colorder asc

OPEN mycursor1                

FETCH NEXT from mycursor1
into @ColDEF

WHILE @@FETCH_STATUS = 0    

BEGIN
    set @TableCreate = @ColDEF

        FETCH NEXT from mycursor1
    into @ColDEF

    IF @@FETCH_STATUS = 0

    print @TableCreate + ','

    else

    print @TableCreate + ')'

END

CLOSE mycursor1

DEALLOCATE mycursor1

print ''

print '--添加记录'

declare @DataIN varchar(8000)

DECLARE mycursor2
cursor FOR SELECT a.name
FROM syscolumns a
left join systypes b
on a.xtype = b.xusertype   
inner join sysobjects d     
on a.id = d.id
where d.name = @TableName
order by a.colorder asc

OPEN mycursor2

FETCH NEXT from mycursor2
into @ColDEF

set @DataIN = 'insert into ' + @TableName + ' (' + char(13) + char(10)

WHILE @@FETCH_STATUS = 0    

BEGIN

set @DataIN = @DataIN + @ColDEF

FETCH NEXT from mycursor2
into @ColDEF

if @@FETCH_STATUS = 0

set @DataIN = @DataIN + ',' + char(13) + char(10)

else

set @DataIN = @DataIN + ')' + char(13) + char(10)

end

CLOSE mycursor2

DEALLOCATE mycursor2

print @DataIN