Solved

How do I  search all SQL tables for a value?

Posted on 2007-12-04
2
2,181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

615 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