在线咨询

[昨天]

SQL SERVER 整个数据库所有表都导出。出，数据每个表要一百条数据。如何实现，具体方法。。谢谢

[geniuswjt]

这条件好像除了手动不知到了- -

[昨天]

sql2000要怎么改？

[Warren]

从Sys.Objects 中取出所有type = 'U'的对象（table），然后用游标loop这个查询结果，拼写类似于
select * into table 的子句，下面代码是在同一个Server上的操作，如果需要跨server操作，需要加入一个@LinkedServer变量来控制。

declare @SourceDBName nvarchar(125);
declare @SourceTableName nvarchar(125);
declare @SourceSchemaName nvarchar(125);

declare @SQLCmd  nvarchar(max);
declare @TargetDBName nvarchar(125);
declare @TargetSchemaName nvarchar(125);

set @SourceDBName = 'SourceDB';
set @TargetDBName = 'TargetDB';
set @TargetSchemaName = 'dbo';

-- help out all user tables
declare  curTables cursor for
select 
    obj.name as TableName
   ,sch.name  as SchemaName
from sys.objects obj
join sys.schemas sch
  on sch.schema_id = obj.schema_id
where obj.type = 'U';

open curTables

fetch next from curTables 
into @SourceTableName, @SourceSchemaName;

-- loop select top 100* into
while @@FETCH_STATUS = 0
begin
    if OBJECT_ID(@TargetDBName + '.' + @TargetSchemaName + '.' + @SourceTableName, 'U') is not null
    begin
      set @SQLCmd = 'drop table ' + @TargetDBName + '.' + @TargetSchemaName + '.' + @SourceTableName;
      exec sp_executesql @SQLCmd
    end
    set @SQLCmd = 'select top 100 *' + ' ' +
									'into ' + @TargetDBName + '.' + @TargetSchemaName + '.' + @SourceTableName + ' ' +
									'from ' + @SourceSchemaName + '.' + @SourceTableName 
									
    exec sp_executesql @SQLCmd ;
    
    fetch next from curTables 
		into @SourceTableName, @SourceSchemaName;
end 

close curTables;
deallocate curTables;