日期:2014-05-18  浏览次数:20696 次

可以用一条SQL语句改变整个数据中所有数据库表中的相同字段的值吗?
可以用一条SQL语句改变整个数据中所有数据库表中的相同字段的值吗? 

假如我有一个数据库A 里面有10张表 有的7张表有字段NAME 但是我不知道是哪7张表有这字段 所以我想用一条SQL语句去实现 直接修改掉整个数据中这个字段的值  


------解决方案--------------------
SQL code
EXEC SP_MSFOREACHTABLE 'UPDATE ? SET [NAME]='目标值''

------解决方案--------------------
也可以拼语句,不过要写的比上面多,你要的话可以写给你
------解决方案--------------------
以下语句可以查到是哪几张表中有这个字段
SQL code
SELECT a.name AS Table_Name, b.name AS Column_Name, c.name AS Variable_Type, b.xtype, b.xusertype, b.length
FROM  sys.sysobjects AS a WITH (nolock) 
INNER JOIN sys.syscolumns AS b WITH (nolock) ON b.id = a.id 
LEFT OUTER JOIN sys.systypes AS c WITH (nolock) ON b.xtype = c.xtype AND b.xusertype = c.xusertype
WHERE (a.type = 'U') and b.name='你要找的列名'

------解决方案--------------------
EXEC sp_MSforeachtable @whereand='and exists(select * from syscolumns where id = o.id and name = ''name'')',
@replacechar='*',
@precommand='print ''Updating .....''',
@command1='print ''*'' update * set name = ''&*%^%##''',
@postcommand= 'print ''Complete Update !'''



------解决方案--------------------
SQL code
EXEC sp_MSforeachtable @whereand='and exists(select * from syscolumns where id = o.id and name = ''name'')',
  @replacechar='*',
  @precommand='print ''Updating .....''',
  @command1='print ''*'' update * set name = ''&*%^%##''',
  @postcommand= 'print ''Complete Update !'''

------解决方案--------------------
把以下语句的执行结果Copy出来执行一次就能达到你的要求了
SQL code
SELECT a.name AS Table_Name, b.name AS Column_Name, c.name AS Variable_Type, b.xtype, b.xusertype, b.length
into #temp
FROM  sys.sysobjects AS a WITH (nolock) 
INNER JOIN sys.syscolumns AS b WITH (nolock) ON b.id = a.id 
LEFT OUTER JOIN sys.systypes AS c WITH (nolock) ON b.xtype = c.xtype AND b.xusertype = c.xusertype
WHERE (a.type = 'U') and b.name='你要找的列名'

select 'update '+Table_Name+' set '+Column_Name+'=''字段要改成的值''' from #temp

------解决方案--------------------
学习了