背景:在做完一个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();
}
}