查看完整版本: [-- 搜索指定字符串在哪些表和字段存在 --]

风信Java论坛 ›› Microsoft SQL Server ›› 搜索指定字符串在哪些表和字段存在 登录 -> 注册

1F 搜索指定字符串在哪些表和字段存在   唧唧 Post by : 2011-03-14 17:34:26.0
   sql代码
  1. ------------------第一种方法---------------------- 
  2.  
  3. CREATE PROC sp_ValueSearch 
  4. @value sql_variant,  --要搜索的数据 
  5. @precision bit=1     --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配 
  6. AS 
  7. SET NOCOUNT ON 
  8. IF @value IS NULL RETURN 
  9.  
  10. --数据类型处理 
  11. SELECT xtype INTO #t FROM systypes 
  12. WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType') 
  13.  
  14. --扩展数据类型及查询处理语句 
  15. DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000) 
  16. IF @precision=1 
  17.     SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType') 
  18.         WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%''' 
  19.         WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%''' 
  20.         ELSE N'=@value' END 
  21. ELSE 
  22. BEGIN 
  23.     SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname) 
  24.     IF @sql LIKE N'%char' or @sql LIKE N'%text' 
  25.     BEGIN 
  26.         INSERT #t SELECT xtype FROM systypes 
  27.         WHERE name LIKE N'%char' or name LIKE N'%text' 
  28.         SELECT @sql=N' LIKE N''%''+CAST(@value as ' 
  29.             +CASE  
  30.                 WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)' 
  31.                 ELSE 'varchar(8000)' END 
  32.             +N')+N''%''' 
  33.     END 
  34.     ELSE IF @sql LIKE N'%datetime' 
  35.     BEGIN 
  36.         INSERT #t SELECT xtype FROM systypes 
  37.         WHERE name LIKE N'%datetime' 
  38.         SET @sql=N'=@value'     
  39.     END 
  40.     ELSE IF @sql LIKE N'%int'  
  41.         OR @sql LIKE N'%money'  
  42.         OR @sql IN(N'real',N'float',N'decimal',N'numeric') 
  43.     BEGIN 
  44.         INSERT #t SELECT xtype FROM systypes 
  45.         WHERE name LIKE N'%int'  
  46.             OR name LIKE N'%money'  
  47.             OR name IN(N'real',N'float',N'decimal') 
  48.         SET @sql=N'=@value'     
  49.     END 
  50.     ELSE 
  51.         SET @sql=N'=@value' 
  52. END 
  53. --保存结果的临时表 
  54. CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000)) 
  55.  
  56. DECLARE tb CURSOR LOCAL 
  57. FOR 
  58. SELECT N'SELECT * FROM ' 
  59.     +QUOTENAME(USER_NAME(o.uid)) 
  60.     +N'.'+QUOTENAME(o.name) 
  61.     +N' WHERE '+QUOTENAME(c.name) 
  62.     +@sql, 
  63.     N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''') 
  64.     +N',N'+QUOTENAME(c.name,N'''') 
  65.     +N',N'+QUOTENAME(QUOTENAME(t.name)+CASE  
  66.         WHEN t.name IN (N'decimal',N'numeric') 
  67.         THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')' 
  68.         WHEN t.name=N'float' 
  69.             OR t.name like N'%char' 
  70.             OR t.name like N'%binary' 
  71.         THEN N'('+CAST(c.prec as varchar)+N')' 
  72.         ELSE N'' END,N'''') 
  73.     +N',@sql)' 
  74. FROM sysobjects o,syscolumns c,systypes t,#t tt 
  75. WHERE o.id=c.id 
  76.     AND c.xusertype=t.xusertype 
  77.     AND t.xtype=tt.xtype 
  78.     AND OBJECTPROPERTY(o.id,N'IsUserTable')=1 
  79.  
  80. OPEN tb 
  81. FETCH tb INTO @sql,@sql1 
  82. WHILE @@FETCH_STATUS=0 
  83. BEGIN 
  84.     SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1 
  85.     EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql 
  86.     FETCH tb INTO @sql,@sql1 
  87. END 
  88. CLOSE tb 
  89. DEALLOCATE tb 
  90. SELECT * FROM # 
  91.  
  92. exec sp_ValueSearch '要搜索的值',1 --1或不输入（即默认值1）精确匹配 
  93. exec sp_ValueSearch '要搜索的值',0 --不等于1，模糊匹配 
  94.  
  95. ---------------第二种方法------------------ 
  96.  
  97. Create   PROC xb_GetTableNameAndColNameForValue 
  98.  @value varchar(200) 
  99. AS 
  100. --求test库中包含值为@value的表和列名 
  101.  
  102. --存储表名和列名 
  103. IF object_id('tabss') IS NOT NULL  
  104.  exec('drop table tabss') 
  105. CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100)) 
  106.  
  107. --查询某表某列是否包含某个值 
  108. IF object_id('ysgs') IS NOT NULL 
  109.  exec('drop proc ysgs') 
  110. exec('create PROC ysgs(@tab varchar(100),@col varchar(100)) 
  111. AS 
  112. exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')') 
  113.  
  114. --将结果存入tabss表中 
  115. 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'')) 
  116. AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test' 
  117.  
  118.  
  119. GO 
  120.  
  121.  
  122. /*调用 
  123. exec xb_GetTableNameAndColNameForValue 'aa_1' 
  124. SELECT * FROM tabss 
  125. */ 
  126.  
  127. ------------------------第三种方法--------------------- 
  128. declare @name nvarchar(100) 
  129. declare cur cursor for select name from sysobjects where type = 'U' 
  130. open cur 
  131. fetch next from cur into @name 
  132. WHILE @@FETCH_STATUS = 0 
  133. begin 
  134.  
  135.     declare @sql nvarchar(500),@s varchar(500) 
  136.     set @s ='' 
  137.     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) '  
  138.     exec sp_executesql @sql,N'@s varchar(500) out',@s out 
  139.     if len(@s) > 0  
  140.         exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print '''+@name+'''') 
  141.     fetch next from cur into @name 
  142. end 
  143. close cur 
  144. DEALLOCATE cur 
  145.  
  146. ------第4种方法---------（来自小梁） 
  147.  
  148. CREATE TABLE tb(id int,col varchar(20)) 
  149. CREATE TABLE tb2(id int,data varchar(20)) 
  150.  
  151. INSERT tb VALUES(1,'中国'); 
  152. INSERT tb VALUES(2,'liangck') 
  153.  
  154. INSERT tb2 VALUES(1,'China') 
  155. GO 
  156.  
  157. CREATE PROCEDURE dbo.FindString 
  158.     @string NVARCHAR(100) 
  159. AS 
  160.  
  161.     DECLARE @SQL NVARCHAR(4000); 
  162.     SET @SQL = N' 
  163.             DECLARE @str NVARCHAR(4000); 
  164.             SELECT 
  165.                 @str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%'  
  166.                                                 + @string + ' %'''''', 
  167.                                c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'') 
  168.                 AND c.xtype=t.xtype 
  169.                 AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar''); 
  170.  
  171.             SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str; 
  172.             CREATE TABLE #tb(a int); 
  173.             INSERT #tb(a) EXEC(@str); 
  174.             IF EXISTS(SELECT * FROM #tb) 
  175.                 PRINT ''?'' 
  176.         '; 
  177.     EXEC sp_MsforeachTable @SQL; 
  178. GO 
  179.  
  180. EXEC dbo.FindString N'中国' 
  181.  
  182. GO 
  183. DROP PROCEDURE dbo.FindString 
  184. DROP TABLE tb,tb2 

 

风信Java论坛 ›› Microsoft SQL Server ›› 搜索指定字符串在哪些表和字段存在 登录 -> 注册

查看完整版本: [-- 搜索指定字符串在哪些表和字段存在 --]
CopyRight © 2008-2009 JavaWind.Net Studio All Rights Reserved
Powered By JWind.BBS Vesion 1.0.0 Beta1 Processed in 56 ms,0 (Queries)  Gzip enabled
粤ICP备07511478号