Search for table that have fields with spesific values

Posted on 2011-10-14
Medium Priority
Last Modified: 2012-05-12
I'm working (end user) with an application that have a big database. I want to find out which table some of the fields I have in the application is saved. Excample: I want to find the name of every table where the value "1234.50" is stored in one of the fields.  I know how to run sql from Management Studio. Is it possible to write a sql that search every table in the database for a spesific value?
Question by:hallpett
LVL 10

Accepted Solution

plummet earned 2000 total points
ID: 36967313

There is an article that shows how this can be done: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2

See paragraph 2 for details.

Hope it helps

Expert Comment

ID: 36967314
Try this store procedure

Execute store procedure as

exec GetAllTables '1234.50'
-- GetAllTables '1234.50'
create proc GetAllTables 
@inputValue nvarchar(1000)
declare @tabeCollection table(Name nvarchar(1000))
declare @TableName nvarchar(100), @Column_name nvarchar(100)
declare @count int, @strSql nvarchar(max)
DECLARE @retCount int
declare cur cursor for 
select Table_Name,column_name from INFORMATION_SCHEMA.COLUMNS 
open cur
fetch next from cur into @TableName, @Column_name
while @@FETCH_STATUS =0
	print 'select count(*) from ' + @TableName + ' where ' + @Column_name + ' like ' + char(39) + convert(nvarchar,@inputValue) + CHAR(39)
	set @strSql = 'select @retCount = count(*) from ' + @TableName + ' where ' + @Column_name + ' like ' + char(39) + convert(nvarchar,@inputValue) + CHAR(39)
	exec sp_executesql @strSql,N'@retCount int output',@retCount=@retCount output 
	if @retCount > 0
		insert into @tabeCollection
		select @TableName 
fetch next from cur into @TableName, @Column_name
select * from @tabeCollection 
close cur
deallocate cur

Open in new window


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

609 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