想在mssql查询一条数据..在哪里个表里,.这句子怎样写

funders

vpswz 发表于 2018-8-20 16:42

1. USE 数据库名
   
2. GO
   
3. 
   
4. DECLARE @key VARCHAR(30)
   
5. SET @key = 'admin'
   
6. DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)
   
7. DECLARE @sql VARCHAR(2000)
   
8. DECLARE @tsql VARCHAR(8000)
   
9. 
   
10. DECLARE tabCursor CURSOR FOR
    
11. SELECT name FROM sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'
    
12. OPEN tabCursor
    
13. FETCH NEXT FROM tabCursor INTO @tabName
    
14. WHILE @@fetch_status = 0
    
15. BEGIN
    
16.         SET @tsql = ''
    
17.         DECLARE colCursor CURSOR FOR
    
18.         SELECT Name FROM SysColumns WHERE id=Object_Id(@tabName) and xtype=167
    
19.         OPEN colCursor
    
20.         FETCH NEXT FROM colCursor INTO @colName
    
21.         WHILE @@fetch_status = 0
    
22.         BEGIN
    
23.                 SET @sql = 'if(exists(select * from ' + @tabName + ' where '
    
24.                 SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin  select * from '
    
25.                 SET @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''
    
26.                 + @tabName + ''' as TableName  end'
    
27.                 SET @tsql = @tsql + @sql + ';'
    
28.                
    
29.                 FETCH NEXT FROM colCursor INTO @colName
    
30.         END
    
31.         EXEC(@tsql)
    
32.         CLOSE colCursor
    
33.         DEALLOCATE colCursor
    
34.        
    
35.         FETCH NEXT FROM tabCursor INTO @tabName
    
36. END
    
37. CLOSE tabCursor
    
38. DEALLOCATE tabCursor

复制代码

你再到其他库试试？