DECLARE @TABLENAME VARCHAR(50), @COLUMN VARCHAR(50), @SQL1 VARCHAR(500) DECLARE Cur_AllTable CURSOR FOR SELECT a.name,b.name FROM sysobjects a,sys.all_columns b WHERE Upper(a.Type) = 'U' and b.name like '%单位%' ORDER BY a.name,b.nameOPEN Cur_AllTableFETCH NEXT FROM Cur_AllTable INTO @TABLENAME,@COLUMN /*循环执行*/ WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL1 = ' select CASE WHEN @COLUMN LIKE '%美女%' then '有' else '没有' end from ' + @TABLENAME EXECUTE(@SQL1) FETCH NEXT FROM Cur_AllTable INTO @TABLENAME ENDCLOSE Cur_AllTable DEALLOCATE Cur_AllTable GO
create table tb(单位 nvarchar(10)) insert into tb select '美女' go declare @sql nvarchar(4000) set @sql='' select @sql=@sql+'update '+ a.name +' set 单位=''美男'' where 单位=''美女'';' from sys.objects a inner join sys.columns b on a.object_id=b.object_id where b.name='单位' exec(@sql) go select * from tb go drop table tb昨天你帮你写的,这次不用变成美男,,查到有数据就行了。
实在是没有想到一句的。 declare @sql nvarchar(max) set @sql= N' if exists(select 1 from (' + left(cast((select N'select * from '+[name]+N'.sys.all_columns where object_id in (select object_id([name]) from '+name+'.sys.all_objects where type=''u'')'+' union all ' from master.sys.databases where name<>N'ReportServer' and name<>N'ReportServerTempDB' --我是2008,所以这两个数据库不行,如果是2005好像没有这两个数据库 for xml path('')) as nvarchar(max)), len( cast((select N'select * from '+[name]+N'.sys.all_columns where object_id in (select object_id([name]) from '+name+'.sys.all_objects where type=''u'')'+' union all ' from master.sys.databases where name<>N'ReportServer' and name<>N'ReportServerTempDB' --我是2008,所以这两个数据库不行,如果是2005好像没有这两个数据库 for xml path('')) as nvarchar(max)) )-len(N'union all ')) + N') x where x.name like ''%美女%'' ) select ''有'' else select ''没有'' 'exec(@sql)
-- 简洁一点的 declare @sql nvarchar(max) set @sql=cast((select 'select * from '+[name]+'.sys.all_columns where object_id in (select object_id([name]) from '+name+'.sys.all_objects where type=''u'')'+' union all ' from master.sys.databases where name<>'ReportServer' and name<>'ReportServerTempDB' --我是2008,所以这两个数据库不行,如果是2005好像没有这两个数据库 for xml path('')) as nvarchar(max)) set @sql=left(@sql,len(@sql)-len('union all ')) set @sql=' if exists(select 1 from ('+@sql+') x where x.name like ''%美女%'' ) select ''有'' else select ''没有'' ' exec(@sql)
消息 102,级别 15,状态 1,第 1 行 'u' 附近有语法错误。还有不要用like行不行
create table tableMX(id int,单位 varchar(10)) insert into tableMX select 1,'10个美女' union all select 2,'1个国王' go create table tableMY(id int,单位 varchar(10)) insert into tableMY select 1,'10个女' union all select 2,'1个王'go --如查表中包含'单位'字段的所有表 declare @ColName nvarchar(20) SET @ColName = '单位' SELECT cast(a.name as varchar(20)) 表名 , cast(b.name as varchar(20)) 列名 FROM sysobjects a , syscolumns b WHERE a.id = b.id AND b.name = @ColName AND a.type = 'U'/* 表名 列名 -------------------- -------------------- tableMX 单位 tableMY 单位 */
create table tb(单位 nvarchar(10)) insert into tb select '美女' create table tb1(单位 nvarchar(10)) insert into tb select '帅哥' go declare @sql nvarchar(4000) set @sql='' select @sql=@sql+'select '''+a.name+'''as tbname,(case when exists(select 1 from '+ a.name +' where 单位=''美女'') then ''有美女'' else ''没有美女'' end) as mn union all ' from sysobjects a inner join syscolumns b on a.id=b.id where b.name='单位' set @sql=left(@sql,len(@sql)-10) exec(@sql) go drop table tb,tb1 /* tbname mn ------ -------- tb 有美女 tb1 没有美女(2 行受影响) */
create table tb(单位 nvarchar(10)) insert into tb select '美女' create table tb1(单位 nvarchar(10)) insert into tb select '帅哥' go declare @sql nvarchar(4000) set @sql='' select @sql=@sql+'select '''+a.name+'''as tbname,''单位''as oh,(case when exists(select 1 from '+ a.name +' where 单位=''美女'') then ''有美女'' else ''没有美女'' end) as mn union all ' from sysobjects a inner join syscolumns b on a.id=b.id where b.name='单位' set @sql=left(@sql,len(@sql)-10) exec(@sql) go drop table tb,tb1 /* tbname oh mn ------ ---- -------- tb 单位 有美女 tb1 单位 没有美女(2 行受影响) */
DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128); SET @schemaname = N'dbo'; SET @tablename = N'tableMX';--把这个表动态循环即可 DECLARE @objectname AS NVARCHAR(517); SET @objectname = QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename); exec(N'select '''+@tablename+''' as TableName, ''单位'' as ColName,case when count(1)>0 then ''有'' else ''无'' end as Result from ' + @objectname + N' where charindex(''美女'',单位)>0') /* TableName ColName Result --------- ------- ------ tableMX 单位 有*/
@COLUMN VARCHAR(50),
@SQL1 VARCHAR(500)
DECLARE Cur_AllTable CURSOR FOR SELECT a.name,b.name
FROM sysobjects a,sys.all_columns b
WHERE Upper(a.Type) = 'U' and b.name like '%单位%'
ORDER BY a.name,b.nameOPEN Cur_AllTableFETCH NEXT FROM Cur_AllTable
INTO @TABLENAME,@COLUMN
/*循环执行*/
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL1 = ' select CASE WHEN @COLUMN LIKE '%美女%' then '有' else '没有' end from ' + @TABLENAME
EXECUTE(@SQL1)
FETCH NEXT FROM Cur_AllTable
INTO @TABLENAME
ENDCLOSE Cur_AllTable
DEALLOCATE Cur_AllTable
GO
insert into tb select '美女'
go
declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+'update '+ a.name +' set 单位=''美男'' where 单位=''美女'';'
from sys.objects a inner join sys.columns b on a.object_id=b.object_id where b.name='单位'
exec(@sql)
go
select * from tb
go
drop table tb昨天你帮你写的,这次不用变成美男,,查到有数据就行了。
declare @sql nvarchar(max)
set @sql=
N' if exists(select 1 from ('
+
left(cast((select N'select * from '+[name]+N'.sys.all_columns where object_id in (select object_id([name]) from '+name+'.sys.all_objects where type=''u'')'+' union all '
from master.sys.databases
where name<>N'ReportServer' and name<>N'ReportServerTempDB' --我是2008,所以这两个数据库不行,如果是2005好像没有这两个数据库
for xml path('')) as nvarchar(max)),
len(
cast((select N'select * from '+[name]+N'.sys.all_columns where object_id in (select object_id([name]) from '+name+'.sys.all_objects where type=''u'')'+' union all '
from master.sys.databases
where name<>N'ReportServer' and name<>N'ReportServerTempDB' --我是2008,所以这两个数据库不行,如果是2005好像没有这两个数据库
for xml path('')) as nvarchar(max))
)-len(N'union all '))
+
N') x where x.name like ''%美女%'' ) select ''有'' else select ''没有'' 'exec(@sql)
-- 简洁一点的
declare @sql nvarchar(max)
set @sql=cast((select 'select * from '+[name]+'.sys.all_columns where object_id in (select object_id([name]) from '+name+'.sys.all_objects where type=''u'')'+' union all '
from master.sys.databases
where name<>'ReportServer' and name<>'ReportServerTempDB' --我是2008,所以这两个数据库不行,如果是2005好像没有这两个数据库
for xml path('')) as nvarchar(max))
set @sql=left(@sql,len(@sql)-len('union all '))
set @sql=' if exists(select 1 from ('+@sql+') x where x.name like ''%美女%'' ) select ''有'' else select ''没有'' '
exec(@sql)
消息 102,级别 15,状态 1,第 1 行
'u' 附近有语法错误。还有不要用like行不行
create table tableMX(id int,单位 varchar(10))
insert into tableMX
select 1,'10个美女' union all
select 2,'1个国王'
go
create table tableMY(id int,单位 varchar(10))
insert into tableMY
select 1,'10个女' union all
select 2,'1个王'go
--如查表中包含'单位'字段的所有表
declare @ColName nvarchar(20)
SET @ColName = '单位'
SELECT cast(a.name as varchar(20)) 表名 ,
cast(b.name as varchar(20)) 列名
FROM sysobjects a ,
syscolumns b
WHERE a.id = b.id
AND b.name = @ColName
AND a.type = 'U'/*
表名 列名
-------------------- --------------------
tableMX 单位
tableMY 单位
*/
insert into tb select '美女'
create table tb1(单位 nvarchar(10))
insert into tb select '帅哥'
go
declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+'select '''+a.name+'''as tbname,(case when exists(select 1 from '+ a.name +' where 单位=''美女'') then ''有美女'' else ''没有美女'' end) as mn union all '
from sysobjects a inner join syscolumns b on a.id=b.id where b.name='单位'
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
go
drop table tb,tb1
/*
tbname mn
------ --------
tb 有美女
tb1 没有美女(2 行受影响)
*/
insert into tb select '美女'
create table tb1(单位 nvarchar(10))
insert into tb select '帅哥'
go
declare @sql nvarchar(4000)
set @sql=''
select @sql=@sql+'select '''+a.name+'''as tbname,''单位''as oh,(case when exists(select 1 from '+ a.name +' where 单位=''美女'') then ''有美女'' else ''没有美女'' end) as mn union all '
from sysobjects a inner join syscolumns b on a.id=b.id where b.name='单位'
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
go
drop table tb,tb1
/*
tbname oh mn
------ ---- --------
tb 单位 有美女
tb1 单位 没有美女(2 行受影响)
*/
DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);
SET @schemaname = N'dbo';
SET @tablename = N'tableMX';--把这个表动态循环即可
DECLARE @objectname AS NVARCHAR(517);
SET @objectname = QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename);
exec(N'select '''+@tablename+''' as TableName, ''单位'' as ColName,case when count(1)>0 then ''有'' else ''无'' end as Result
from ' + @objectname + N' where charindex(''美女'',单位)>0')
/*
TableName ColName Result
--------- ------- ------
tableMX 单位 有*/