|
------------------第一种方法----------------------
CREATE PROC sp_ValueSearch
@value sql_variant,? --要搜索的数据
@precision bit=1???? --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN
--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')
--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
??? SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
??????? WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
??????? WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
??????? ELSE?N'=@value'?END
ELSE
BEGIN
??? SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
??? IF @sql LIKE N'%char' or @sql LIKE N'%text'
??? BEGIN
??????? INSERT #t SELECT xtype FROM systypes
??????? WHERE name LIKE N'%char' or name LIKE N'%text'
??????? SELECT @sql=N' LIKE N''%''+CAST(@value as '
??????????? +CASE?
??????????????? WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
??????????????? ELSE 'varchar(8000)' END
??????????? +N')+N''%'''
??? END
??? ELSE IF @sql LIKE N'%datetime'
??? BEGIN
??????? INSERT #t SELECT xtype FROM systypes
??????? WHERE name LIKE N'%datetime'
??????? SET @sql=N'=@value'????
??? END
??? ELSE IF @sql LIKE N'%int'?
??????? OR @sql LIKE N'%money'?
??????? OR @sql IN(N'real',N'float',N'decimal',N'numeric')
??? BEGIN
??????? INSERT #t SELECT xtype FROM systypes
??????? WHERE name LIKE N'%int'?
??????????? OR name LIKE N'%money'?
??????????? OR name IN(N'real',N'decimal')
??????? SET @sql=N'=@value'????
??? END
??? ELSE
??????? SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))
DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
??? +QUOTENAME(USER_NAME(o.uid))
??? +N'.'+QUOTENAME(o.name)
??? +N' WHERE '+QUOTENAME(c.name)
????+@sql,
??? N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
??? +N',N'+QUOTENAME(c.name,N'+QUOTENAME(QUOTENAME(t.name)+CASE?
??????? WHEN t.name IN (N'decimal',N'numeric')
??????? THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
??????? WHEN t.name=N'float'
??????????? OR t.name like N'%char'
??????????? OR t.name like N'%binary'
??????? THEN N'('+CAST(c.prec as varchar)+N')'
??????? ELSE N'' END,@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
??? AND c.xusertype=t.xusertype
??? AND t.xtype=tt.xtype
??? AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
??? SET @sql1=N'IF EXISTS('+@sql+N')?'+@sql1
??? EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
??? FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #
exec sp_ValueSearch '要搜索的值',1 --1或不输入（即默认值1）精确匹配
exec sp_ValueSearch '要搜索的值',0 --不等于1，模糊匹配
?
---------------第二种方法------------------
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
*/
------------------------第三种方法---------------------
declare @name nvarchar(100)
declare cur cursor for select name from sysobjects where type = 'U'
open cur
fetch next from cur into @name
WHILE @@FETCH_STATUS = 0
begin
??? declare @sql nvarchar(500),@s varchar(500)
??? set @s =''
??? set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(''+name+'' as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) '?
??? exec sp_executesql @sql,N'@s varchar(500) out',@s out
??? if len(@s) > 0?
??????? exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print'''+@name+'''')
??? fetch next from cur into @name
end
close cur
DEALLOCATE cur
------第4种方法---------（来自小梁）
CREATE?TABLE?tb(id?int,col?varchar(20))
?tb2(id?))
INSERT?tb?VALUES1'中国');
2liangck)
?tb2?China)
GO
PROCEDURE?dbo.FindString
????@stringNVARCHAR100AS
????DECLARE@SQL4000);
????SET?=?N
??????????? DECLARE @str NVARCHAR(4000);
??????????? SELECT
??????????????? @str = ISNULL(@str + N''?OR??+ c.name + N?LIKE N''''%?
????????????????????????????????????????????????+??%''''''+) FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(?)
??????????????? AND c.xtype=t.xtype
??????????????? AND t.name IN(varcharcharnvarcharnchar);
??????????? SET @str =?SELECT TOP 1 1 FROM ? WHERE?+@str;
??????????? CREATE TABLE #tb(a int);
??????????? INSERT #tb(a) EXEC(@str);
??????????? IF EXISTS(SELECT * FROM #tb)
??????????????? PRINT?
????????;
????EXEC?sp_MsforeachTable?;
?dbo.FindString N
DROP?dbo.FindString
?tb,tb2
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
