批量修改sqlserver 字段默认值

批量修改sqlserver? 字段wid 默认值

?

package com.pm360.pip.test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class TestSqlserver {
?public static void testMS ()throws NamingException {
??Connection conn=null;
??ResultSet rs=null;
??ResultSet rs1=null;
??Statement statement=null;
??Statement statement1=null;
??Statement statement2=null;
??Statement statement3=null;
??try {
???Context initCtx = new InitialContext();
???DataSource ds= (DataSource)? initCtx.lookup("java:/comp/env/jdbc/pip");
???conn=ds.getConnection();
???//得到数据库所有包含WID字段的表
???String sql="SELECT dbo.sysobjects.name AS [table],dbo.syscolumns.name AS columns FROM dbo.sysobjects INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id? and syscolumns.name='WID' WHERE (dbo.sysobjects.xtype = 'u')";
???/*List<Map<String,Object>> listmap=DBUtil.queryListMapBySql(conn,sql);
???for (Map<String,Object> map : listmap) {
????String table=(String)map.get("table");
????String columns=(String)map.get("columns");
????System.out.println(table+"----"+columns);
????
???}*/
????statement=conn.createStatement();
????statement1=conn.createStatement();
????statement2=conn.createStatement();
????statement3=conn.createStatement();
????rs= statement.executeQuery(sql);
???while(rs.next())
???{
????String Tablename = rs.getString(1);
????System.out.println(Tablename);
????System.out.println(rs.getFetchSize());
????String temp="select?? t3.name?? as?? 表名,t1.name?? as?? 字段名,t2.text?? as?? 默认值??,t4.name?? from?? syscolumns?? t1,syscomments?? t2,sysobjects?? t3??,sysobjects?? t4?? where???? t1.cdefault=t2.id?? and?? t3.xtype='u'?? and?? t3.id=t1.id?? and?? t4.xtype='d'?? and?? t4.id=t2.id";
????rs1=statement1.executeQuery(temp);
????while(rs1.next())
????{
?????if(Tablename.equalsIgnoreCase(rs1.getString(1)))
?????{
??????//SQLSERVER修改默认值 必须删除字段的约束
??????String dropdefaultsql="alter?? table "+Tablename+"? drop constraint "+rs1.getString(4)+"";
??????statement3.execute(dropdefaultsql);
?????}
????}
????//添加默认值
????String asql=" alter?? table? "+Tablename+"?? add?? default newid() for? wid ";
????statement2.execute(asql);
???}
??} catch (SQLException e) {
???e.printStackTrace();
????
??}
??finally
??{
???try {
????rs.close();
????rs1.close();
????statement.close();
????conn.close();
???} catch (SQLException e) {
????e.printStackTrace();
???}
??}
?}

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!