sql server 2008图形界面中的问题

右键，选择前1000行，再在代码那里把TOP 1000 去掉，然后执行即可，就是这么麻烦。最好用语句操作。

怎么在面板中全部导出t-sql脚本？

可以导出数据库框架模式脚本，不能直接导出数据的 insert 脚本！要导出数据的 insert 脚本，需依靠第三方存储过程，用下面的代码可以实现！
CREATE PROCEDURE dbo.UspOutputData
@tablename sysname
AS
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectId int
declare @objectname sysname
declare @ident int set nocount on
set @objectId=object_id(@tablename) if @objectId is null -- 判断对象是否存在
begin
print 'The object not exists'
return
end
set @objectname=rtrim(object_name(@objectId)) if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密
begin
print 'object not in current database'
return
end if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是table
begin
print 'The object is not table'
return
end select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' ON' declare syscolumns_cursor cursorfor select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid open syscolumns_cursor
set @column=''
set @columndata=''
fetch next from syscolumns_cursor into @name,@xtype while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 begin
set @column=@column+case when len(@column)=0 then'' else ','end+@name set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','
end +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier
else @name end end end fetch next from syscolumns_cursor into @name,@xtype end close syscolumns_cursor
deallocate syscolumns_cursor set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename print '--'+@sql
exec(@sql) if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF' GO使用方法：exec UspOutputData 你的表名然后将运行后的结果存成.sql，加上用SQL Server生成的数据库脚本就可以了另参考：如何为SQL Server表数据生成insert脚本

导出数据的脚本参考楼上。导出结构脚本的方法你右键数据库-->任务-->生成脚本

--不好找，帮你改了一个批量生成作业的过程，你试试：
create proc Generate_Job_Script_for2008
as
set nocount on
declare @jobname nvarchar(1000)
declare @tb table (id int identity(1,1),sqltext varchar(4000))
declare @job_category_name varchar(1000)
declare @job_id varchar(100)
declare @changeline varchar(10)
declare @enabled int,
    @notify_level_eventlog int,
    @notify_level_email int,
    @notify_level_netsend int ,
    @notify_level_page int,
    @delete_level int,
    @description varchar(1000),
    @category_name varchar(1000),
    @owner_login_name varchar(1000)declare c cursor for
    select name from msdb.dbo.sysjobs
open c
fetch c into @jobname
while @@FETCH_STATUS=0
begin
    set @changeline = ','+char(13)
    select @job_category_name = replace(b.name,'''',''''''),@job_id = a.job_id
    from msdb.dbo.sysjobs a join msdb.dbo.syscategories b on a.category_id = b.category_id where a.name =@jobname    insert into @tb(sqltext)
    select 'USE [msdb]' union all
    select 'GO' union all
    select '/****** 对象: Job ['+@jobname+']    脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/' union all
    select 'BEGIN TRANSACTION' union all
    select 'DECLARE @ReturnCode INT' union all
    select 'SELECT @ReturnCode = 0' union all
    select '/****** 对象: JobCategory ['+@job_category_name+']    脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/' union all
    select 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+@job_category_name+''' AND category_class=1)' union all
    select 'BEGIN' union all
    select 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name=N'''+@job_category_name+'''' union all
    select 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback' union all
    select '' union all
    select 'END'    select
    @enabled                                 = enabled,
    @notify_level_eventlog         =notify_level_eventlog,
    @notify_level_email                 =notify_level_email,
    @notify_level_netsend         =notify_level_netsend,
    @notify_level_page                 =notify_level_page,
    @delete_level                         =delete_level,
    @description                         =description,
    @category_name                         =@job_category_name,
    @owner_login_name                 =suser_sname(owner_sid)
    from msdb.dbo.sysjobs
    where job_id =@job_id    insert into @tb(sqltext)
    select 'DECLARE @jobId BINARY(16)' union all
    select 'EXEC @ReturnCode = msdb.dbo.sp_add_job ' union all
    select '                 @job_name=N'''+@jobname+''', ' union all
    select '                 @enabled='+isnull(convert(varchar(10),@enabled),'')+', ' union all
    select '                 @notify_level_eventlog='+isnull(convert(varchar(10),@notify_level_eventlog),'')+', ' union all
    select '                 @notify_level_email='+isnull(convert(varchar(10),@notify_level_email),'')+', ' union all
    select '                 @notify_level_netsend='+isnull(convert(varchar(10),@notify_level_netsend),'')+', ' union all
    select '                 @notify_level_page='+isnull(convert(varchar(10),@notify_level_page),'')+', ' union all
    select '                 @delete_level='+isnull(convert(varchar(10),@delete_level),'')+', ' union all
    select '                 @description=N'''+isnull(convert(varchar(1000),replace(@description,'''','''''')),'')+''', ' union all
    select '                 @category_name=N'''+isnull(convert(varchar(1000),replace(@category_name,'''','''''')),'')+''', ' union all
    select '                 @owner_login_name=N'''+isnull(convert(varchar(100),replace(@owner_login_name,'''','''''')),'')+''', @job_id = @jobId OUTPUT' union all
    select 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'    if exists( select 1 from msdb.dbo.sysjobsteps where job_id =@job_id)
    begin
    insert into @tb(sqltext)
    select c1 from (
    select 1 as id,step_id,
    '/****** 对象: Step ['+convert(varchar(10),step_id )+']    脚本日期: '+convert(varchar(10),getdate(),101)+' '+convert(varchar(8),getdate(),108)+' ******/'+char(13)+
    'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep '+replace(@changeline,',','') +
    '                 @job_id = @JobID'+@changeline+
    '                 @step_name = N'''+isnull(replace(step_name,'''','''''') ,'')+''''+@changeline+
    '                 @step_id = '+isnull(convert(varchar(10),step_id ),'') + @changeline+
    '                 @cmdexec_success_code = '+isnull(convert(varchar(10),cmdexec_success_code ),'')+ @changeline+
    '                 @on_success_action = '+isnull(convert(varchar(10),on_success_action ),'') + @changeline+
    '                 @on_success_step_id = '+isnull(convert(varchar(10),on_success_step_id ),'') + @changeline+
    '                 @on_fail_action = '+isnull(convert(varchar(10),on_fail_action ),'') + @changeline+
    '                 @on_fail_step_id = '+isnull(convert(varchar(10),on_fail_step_id ),'') + @changeline+
    '                 @retry_attempts = '+isnull(convert(varchar(10),retry_attempts ),'') + @changeline+
    '                 @retry_interval = '+isnull(convert(varchar(10),retry_interval ),'') + @changeline+
    '                 @os_run_priority = '+isnull(convert(varchar(10),os_run_priority ),'') + @changeline+
    '                 @subsystem = N'''+isnull(convert(varchar(100),replace(subsystem,'''','''''') ),'') +''''+ @changeline+
    '                 @command = N'''+isnull(convert(varchar(8000),replace(command,'''','''''') ),'') +''''+ @changeline+
    '                 @database_name = N'''+isnull(convert(varchar(100),replace(database_name,'''','''''') ),'') +''''+ @changeline+
    '                 @flags = '+isnull(convert(varchar(10),flags ),'') + replace(@changeline,',','') as c1    from msdb.dbo.sysjobsteps
    where job_id =@job_id    union all    select 2 as id,step_id, ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' as c1    from msdb.dbo.sysjobsteps
    where job_id =@job_id
    ) abc
    order by step_id,id    end    insert into @tb(sqltext)
    select ' EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = '+isnull(convert(varchar(100),start_step_id),'')+' '
    from msdb.dbo.sysjobs
    where job_id =@job_id union all
    select '' union all
    select ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '
    insert into @tb(sqltext)
    select ''    if exists (select 1 from msdb.dbo.sysjobschedules where job_id =@job_id)
    begin
    --         insert into @tb(sqltext)
    --         select ' -- 添加作业调度'    insert into @tb(sqltext)
    select c1 from (
    select 1 as id,a.schedule_id,
    'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule '+replace(@changeline,',','') +
    '                 @job_id = @JobID'+@changeline+
    '                 @name = N'''+isnull(replace(name,'''','''''') ,'')+''''+@changeline+
    '                 @enabled = '+isnull(convert(varchar(10),enabled ),'') + @changeline+
    '                 @freq_type = '+isnull(convert(varchar(10),freq_type ),'')+ @changeline+
    '                 @freq_interval = '+isnull(convert(varchar(10),freq_interval ),'') + @changeline+
    '                 @freq_subday_type = '+isnull(convert(varchar(10),freq_subday_type ),'') + @changeline+
    '                 @freq_subday_interval = '+isnull(convert(varchar(10),freq_subday_interval ),'') + @changeline+
    '                 @freq_relative_interval = '+isnull(convert(varchar(10),freq_relative_interval ),'') + @changeline+
    '                 @freq_recurrence_factor = '+isnull(convert(varchar(10),freq_recurrence_factor ),'') + @changeline+
    '                 @active_start_date = '+isnull(convert(varchar(100),active_start_date ),'') + @changeline+
    '                 @active_end_date = '+isnull(convert(varchar(100),active_end_date ),'') + @changeline+
    '                 @active_start_time = '+isnull(convert(varchar(100),active_start_time ),'') + @changeline+
    '                 @active_end_time = '+isnull(convert(varchar(100),active_end_time ),'') + replace(@changeline,',','')
    as c1
    from msdb.dbo.sysschedules a join msdb.dbo.sysjobschedules b
    on a.schedule_id = b.schedule_id
    where b.job_id =@job_id    union all    select 2 as id,schedule_id,' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback ' as c1
    from msdb.dbo.sysjobschedules
    where job_id =@job_id
    ) aa
    order by schedule_id,id    end    insert into @tb(sqltext)
    --select ' -- 添加目标服务器' union all
    select
    ' EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'''+case when a.server_id = 0 then '(local)' else b.srvname end +''' '
    from msdb.dbo.sysjobservers a join master.dbo.sysservers b
    on a.server_id = b.srvid
    where job_id =@job_id
    union all
    select ' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback '    insert into @tb(sqltext)
    select 'COMMIT TRANSACTION' union all
    select 'GOTO EndSave' union all
    select 'QuitWithRollback:' union all
    select '    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION' union all
    select 'EndSave:'
    fetch c into @jobname
end
close c
deallocate c
select sqltext from @tb
GO
--原脚本参考：http://hi.baidu.com/jixj/blog/item/5b5f07ec0212abd3b21cb131.html
--调用：
exec Generate_Job_Script_for2008

调试易

sql server 2008图形界面中的问题

解决方案 »