a.表=B.表 and
a.表.列=B.表.列
想以A数据库表结构为依据删除B无用的列
并且以A列的数据类型（ 类型长度）为标准修改B表的数据列的数据类型！
这个要怎么写？
5 个解决方案
#1
删除b表，复制a为b
#2
写个存储吧
DECLARE mycursor Cursor
for
select col.name from syscolumns as col inner join (select * from sysobjects where name='B') as tab on tab.id=col.id
opern mycursor
declare @colName nvarchar(100)
while(1=1)
begin
fetch next from mycursor into @colName
if(@@fetch_status<>0)
begin
break
end
if(if not exists(select col.name from syscolumns as col inner join (select * from sysobjects where name='B') as tab on tab.name=@colName))
begin
alter B DROP @colName
end
end
close mycursor
deallocate mycursor
#3
下面那个换成A表
#4
可能细节出错了 讲下思路 ： 搜索出B的所有列名与A的列名比较 没有就删除。
#5
查系统表sys.columns, 动态产生DDL SQL语句列表.
-- 想以A数据库表结构为依据删除B无用的列
select 'TableName'=a.name,
'ColumnName'=b.name,
'TSQL'='alter table ['+a.name+'] drop column ['+b.name+'] '
from [B数据库].sys.tables a
inner join [B数据库].sys.columns b on a.object_id=b.object_id
where not exists(select 1
from [A数据库].sys.tables c
inner join [A数据库].sys.columns d on c.object_id=d.object_id
where c.name=a.name and d.name=b.name)
#1
删除b表，复制a为b
#2
写个存储吧
DECLARE mycursor Cursor
for
select col.name from syscolumns as col inner join (select * from sysobjects where name='B') as tab on tab.id=col.id
opern mycursor
declare @colName nvarchar(100)
while(1=1)
begin
fetch next from mycursor into @colName
if(@@fetch_status<>0)
begin
break
end
if(if not exists(select col.name from syscolumns as col inner join (select * from sysobjects where name='B') as tab on tab.name=@colName))
begin
alter B DROP @colName
end
end
close mycursor
deallocate mycursor
#3
下面那个换成A表
#4
可能细节出错了 讲下思路 ： 搜索出B的所有列名与A的列名比较 没有就删除。
#5
查系统表sys.columns, 动态产生DDL SQL语句列表.
-- 想以A数据库表结构为依据删除B无用的列
select 'TableName'=a.name,
'ColumnName'=b.name,
'TSQL'='alter table ['+a.name+'] drop column ['+b.name+'] '
from [B数据库].sys.tables a
inner join [B数据库].sys.columns b on a.object_id=b.object_id
where not exists(select 1
from [A数据库].sys.tables c
inner join [A数据库].sys.columns d on c.object_id=d.object_id
where c.name=a.name and d.name=b.name)