Solved

How do I  search all SQL tables for a value?

Posted on 2007-12-04
2
2,157 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - SQL field is defined as text 3 32
SQL Server - Slabs 9 40
SQL Pivot add row totals 2 16
How to join on ID, with prefix? 15 17
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now