Solved

How do I  search all SQL tables for a value?

Posted on 2007-12-04
2
2,161 Views
Last Modified: 2010-04-21
I have a Great Plains 9 database that I need to find a value... it's somewhere in one of the tables but I haven't been able to locate it.  

How do I script a search to iterate through each table looking for a string?
0
Comment
Question by:awsiemieniec
2 Comments
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 500 total points
ID: 20407104
I use this code.  Replace @myValue with the value you're searching for.  If you are looking for numeric data then uncomment the appropriate line.
Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(30), @SQL as nvarchar(1000)
, @SN as varchar(200), @Exact_Match bit
 
Create Table #myTable (Table_Name varchar(200), Column_Name varchar(200), Number_Of_Rows int)
 
-- Replace @myValue with the value you're searching for in the database
Set @myValue = 'mysearchdata' 
-- 0 for LIKE match, 1 for exact match
Set @Exact_Match = 0    
 
Declare myCursor Cursor For
Select T.Table_Name, C.Column_Name, T.Table_Schema
From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C 
On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name
Where T.Table_Name <> 'dtproperties' And Table_Type = 'Base Table'
And C.Data_Type In ('varchar','char','nvarchar','nchar','sql_variant')
--And C.Data_Type In ('text','ntext')
--And C.Data_Type In ('tinyint','int','bigint','numeric','decimal','money','float','smallint','real','smallmoney')
--And C.Data_Type In ('datetime','dmalldatetime')
-- Fields not searched: image, uniqueidentifier, bit, varbinary, binary, timestamp
Open myCursor
Fetch Next From myCursor Into @TN, @CN, @SN
While @@Fetch_Status <> -1
Begin
	If @Exact_Match = 0
		Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] Like ''%' + @myValue + '%'''
	    Else
		Set @SQL = N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] = ''' + @myValue + ''''
	--Print @SQL
	Exec sp_executesql @SQL 
	Fetch Next From myCursor Into @TN, @CN, @SN
End
Close myCursor
Deallocate myCursor
Select * From #myTable Where Number_Of_Rows > 0 Order By Table_Name
Drop Table #myTable

Open in new window

0
 

Author Closing Comment

by:awsiemieniec
ID: 31412709
Excellent help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Simple SQL query from two tables 13 54
sql server insert 12 30
sql server tables from access 18 18
SQL Server Import/Error Wizard error 12 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question