Search for keywords from all tables and all databases

Search for keywords from all tables and all databases

 

-- 1 simple query: Search for keywords in all tables

Declare @ STR varchar (100)
Set @ STR = 'A' -- string to be searched

Declare @ s varchar (8000)
Declare TB cursor local
Select 'if exists (select 1 from ['+ B. name + '] Where [' +. name + '] Like ''%' + @ STR + '% '')
Print ''['+ B. Name +']. ['+ A. Name +'] '''
From syscolumns a join sysobjects B on A. ID = B. ID
Where B. xtype = 'U' and A. Status> = 0
And a. xusertype in (175,239,231,167)
Open TB
Fetch next from TB into @ s
While @ fetch_status = 0
Begin
Exec (@ s)
Fetch next from TB into @ s
End
Close TB
Deallocate TB

 

-- 2 search from all databases

If object_id ('up _ valuesearch') is not null drop proc up_valuesearch
Go
Create proc up_valuesearch @ dB varchar (200), @ STR varchar (100)
As
Begin
Declare @ s varchar (8000)
Declare TB cursor local
Select 'if exists (select 1 from ['+ B. name + '] Where [' +. name + '] Like ''%' + @ STR + '% '')
Print ''['+ @ dB +'] .. ['+ B. Name +']. ['+ A. Name +'] '''
From syscolumns a join sysobjects B on A. ID = B. ID
Where B. xtype = 'U' and A. Status> = 0
And a. xusertype in (175,239,231,167)
Open TB
Fetch next from TB into @ s
While @ fetch_status = 0
Begin
Exec (@ s)
Fetch next from TB into @ s
End
Close TB
Deallocate TB
End
Go
-- Call example
Sp_msforeachdb 'up _ valuesearch ?, ''A '''

 

Other sorting

------------------ Method 1 ----------------------

Create proc sp_valuesearch
@ Value SQL _variant, -- data to be searched
@ Precision bit = 1 -- 1 = search for data columns of the corresponding type only based on the data type in SQL _variant. <> 1. query all compatible columns. Use like to match character data.
As
Set nocount on
If @ value is null return

-- Data Type Processing
Select xtype into # T from policypes
Where name = SQL _variant_property (@ value, n' basetype ')

-- Extend data types and query and processing statements
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 'text' 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 policypes
Where name like n' % char 'or name like n' % text'
Select @ SQL = n' like n' % ''+ Cast (@ value'
+ Case
When left (@ SQL, 1) = n' then' nvarchar (4000 )'
Else 'varchar (8000) 'End
+ N') + N' % '''
End
Else if @ SQL like n' % datetime'
Begin
Insert # T select xtype from policypes
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 policypes
Where name like n' % int'
Or name like n' % money'
Or name in (N 'real', N 'float', N 'decimal ')
Set @ SQL = n' = @ value'
End
Else
Set @ SQL = n' = @ value'
End
-- Save the temporary table of the result
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 '''')
+ N', n' + 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, n '''')
+ N', @ SQL )'
From sysobjects o, syscolumns C, policypes 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 'value to be searched ', 1 -- 1 or not input (that is, the default value 1) exact match
Exec sp_valuesearch 'value to search', 0 -- not equal to 1, fuzzy match

--------------- Method 2 ------------------

Create proc xb_gettablenameandcolnameforvalue
@ Value varchar (200)
As
-- Find the table and column name whose value is @ value in the Test Database

-- Store the table name and column name
If object_id ('tabs') is not null
Exec ('drop table tabss ')
Create Table tabss (ID int identity (100), tabname varchar (100), colname varchar ))

-- Query whether a column in a table contains a value
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 +' % ''' ''') ')

-- Save the result to the tabss table
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 policypes S2 where name in (''char '', ''varchar'', ''nchar '', ''nvarchar ''))
And ID in (select ID from sysobjects S2 where xtype = ''u'') ', N 'test'

Go

/* Call
Exec xb_gettablenameandcolnameforvalue 'aa _ 1'
Select * From tabss
*/

------------------------ Method 3 ---------------------
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

------ 4th Methods --------- (from xiaoliang)

Create Table Tb (ID int, Col varchar (20 ))
Create Table tb2 (ID int, data varchar (20 ))

Insert TB values (1, 'China ');
Insert TB values (2, 'shanghai ')

Insert tb2 values (1, 'China ')
Go

Create procedure DBO. findstring
@ String nvarchar (100)
As

Declare @ SQL nvarchar (4000 );
Set @ SQL = N'
Declare @ STR nvarchar (4000 );
Select
@ STR = isnull (@ STR + n'' or ''+ C. Name + n'' like n''' %'
+ @ String + '% '''''',
C. name + N' like n' ''' % '+ @ string +' % ''') from syscolumns as C join policypes as t on C. id = object_id (''? '')
And C. xtype = T. xtype
And T. Name in (''varchar '', ''char'', ''nvarchar '', ''nchar '');

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 @ SQL;
Go

Exec DBO. findstring n 'China'

Go
Drop procedure DBO. findstring
Drop table TB, tb2