你的表列多不? 如果不多 你可以看你关键字的属性 如果是字符型 那你就不用去查不是字符型的列 比如 关键字为'ab' if exists(select * from tb where patindex('%ab%',字段1) or patindex('%ab%',字段2) or patindex('%ab%',字段3) print '表含有关键字ab' else print '表bu含有关键字ab' 只适合字段不多的情况
Create PROC xb_GetTableNameAndColNameForValue @value varchar(200) AS --求test库中包含值为@value的表和列名--存储表名和列名 IF object_id('tabss') IS NOT NULL exec('drop table tabss') CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100))--查询某表某列是否包含某个值 IF object_id('ysgs') IS NOT NULL exec('drop proc ysgs') exec('create PROC ysgs(@tab varchar(100),@col varchar(100)) AS exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')')--将结果存入tabss表中 EXEC master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0 insert tabss (colname,tabname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar'')) AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test' GO /*调用 exec xb_GetTableNameAndColNameForValue 'aa_1' SELECT * FROM tabss */
if exists(select * from tb where patindex('%ab%',字段1)>0 or patindex('%ab%',字段2)>0 or patindex('%ab%',字段3)>0 print '表含有关键字ab' else print '表bu含有关键字ab' 修改我上面的
如果不多 你可以看你关键字的属性 如果是字符型 那你就不用去查不是字符型的列
比如 关键字为'ab'
if exists(select * from tb
where patindex('%ab%',字段1) or patindex('%ab%',字段2) or patindex('%ab%',字段3)
print '表含有关键字ab'
else
print '表bu含有关键字ab'
只适合字段不多的情况
@value varchar(200)
AS
--求test库中包含值为@value的表和列名--存储表名和列名
IF object_id('tabss') IS NOT NULL
exec('drop table tabss')
CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100))--查询某表某列是否包含某个值
IF object_id('ysgs') IS NOT NULL
exec('drop proc ysgs')
exec('create PROC ysgs(@tab varchar(100),@col varchar(100))
AS
exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')')--将结果存入tabss表中
EXEC master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0 insert tabss (colname,tabname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))
AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test'
GO
/*调用
exec xb_GetTableNameAndColNameForValue 'aa_1'
SELECT * FROM tabss
*/
where patindex('%ab%',字段1)>0 or patindex('%ab%',字段2)>0 or patindex('%ab%',字段3)>0
print '表含有关键字ab'
else
print '表bu含有关键字ab'
修改我上面的