将SQLServer多个数据库中的空表转移到另一个数据库中

背景：在做完一个SQLServer的数据迁移工作后（导出/导入.sql文件），发现每个库中有多个空表，于是想把这些空表转移到另一个库中。
首先，查看数据库中空表的sql语句如下：

USE 库名;
SELECT  A.NAME

FROM sys.tables A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

GROUP BY A.name

HAVING MAX(B.rows) = 0

查看非空表的sql语句：

USE 库名

SELECT  A.NAME

FROM sys.tables A

INNER JOIN sys.partitions B ON A.object_id = B.object_id

GROUP BY A.name

HAVING MAX(B.rows) > 0

这里查询出的结果只有一列，即空表的表名。

复制表到目标库：

select * into 目标库.dbo.新表名 from 原表名

根据表名查看是否建立了外键索引：

select
    fk.name as name,OBJECT_NAME(fk.parent_object_id) as referenceTableName
from sys.foreign_keys as fk
join sys.objects as o on fk.referenced_object_id=o.object_id
where o.name='表名'（这里的引号必须有）

删除外键索引：

use 库名
ALTER TABLE dbo.表名 DROP CONSTRAINT 外键索引名称

然后通过Java代码来操作（这里写在一起，实际工作中建议写一个工具类，定义连接/查询/修改等方法）：

import java.io.IOException;
import java.sql.*;

public class removeTable {
    static Connection con = null;
    static PreparedStatement pst= null;
    public static void main(String[] args) throws IOException, SQLException, InterruptedException {

        String[] arr = {"库名1","库名2","..."};//定义一个数组将所有要移动空表的数据库名放进去
        String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url = "jdbc:sqlserver://主机名或IP;DatabaseName=";
        String user ="用户名";
        String password = "密码";


        for (String s : arr) {
            //连接数据库
            try {
                Class.forName(driver);
                con = DriverManager.getConnection(url + s, user, password);
                System.out.println("连接到" + s + "数据库");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //查询数据库下空表，获取表名集合
            ResultSet emptyTableSet=null;
            try {
                pst=con.prepareStatement("SELECT A.NAME FROM sys.tables A INNER JOIN sys.partitions B ON A.object_id = B.object_id GROUP BY A.name HAVING MAX(B.rows) = 0");
                emptyTableSet = pst.executeQuery();

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
            }
            //遍历集合，复制空表到目标库，并删除原库中的空表
            int tableNum = 0;//定义一个变量来记录空表数
            while(emptyTableSet.next()){
                String tbName=emptyTableSet.getString("NAME");//获取空表名
                //有些表建立了外键索引，无法直接删除，必须删除外键索引
                //先根据表名查看是否有外键索引的sql语句，这里涉及到在字符串拼接变量且变量要加引号，采用'“+变量名+”'
                String selectsql = "select fk.name as name,OBJECT_NAME(fk.parent_object_id) as referenceTableName from sys.foreign_keys as fk join sys.objects as o on fk.referenced_object_id=o.object_id where o.name='"+tbName+"'";
                System.out.println(selectsql);
                //将空表复制到目标库的sql，新表名采用原库名+表名拼接的形式
                String sql1 = "select * into "+"新库名"+".dbo."+s+"_"+tbName+" from "+tbName;
                //删除原空表的sql
                String sql2 = "drop table "+tbName;
                //查看是否有外键索引，如果有，返回一个结果集，每行数据包括与其建立外键索引的表名和索引名
                pst = con.prepareStatement(selectsql);
                ResultSet resultSet = pst.executeQuery();
                //复制空表
                pst = con.prepareStatement(sql1);
                pst.execute();
                //如果有外键索引，依次进行删除
                while(resultSet.next()){
                    //获取与该表建立外键索引的表名
                    String refName = resultSet.getString("name");
                    //获取外键索引名称
                    String tableName = resultSet.getString("referenceTableName");
                    //删除外键索引
                    pst = con.prepareStatement("ALTER TABLE dbo."+tableName+ " DROP CONSTRAINT "+refName);
                    pst.execute();
                }
                resultSet.close();
                //删除空表
                pst = con.prepareStatement(sql2);
                pst.execute();
                tableNum++;
                Thread.sleep(1);
            }
            System.out.println(tableNum);

            emptyTableSet.close();
        }
        con.close();
    }
}