[Show all top banners]

phone
Replies to this thread:

More by phone
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Discussion Refresh page to view new replies
 How to search column using value in sql server
[VIEWED 3639 TIMES]
SAVE! for ease of future access.
Posted on 09-18-18 9:25 AM     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Hello SQL Gurus,

I have a value for a column. How do I find database, Table and Column has the Value. What can be the query that I can use to locate relevant Database.Table.Column for the given value in the SQL Server?
For eg:-
I have a value '123' now I need to find which Database.Table.Column has the given value.

Thanks in advance
 
Posted on 09-18-18 9:32 AM     [Snapshot: 7]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Just curious - In what scenario does it happen ?
 
Posted on 09-18-18 9:36 AM     [Snapshot: 5]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

USE master
GO

CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname
AS

BEGIN TRY
DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE '

SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE '' + @stringToFind + '' OR '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema
AND TABLE_NAME = @table
AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
EXEC (@sqlCommand)
PRINT @sqlCommand
END TRY

BEGIN CATCH
PRINT 'There was an error. Check to make sure object exists.'
PRINT error_message()
END CATCH

 
Posted on 09-18-18 11:27 AM     [Snapshot: 78]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Thanks Dev_ I ran your skript and got 'Command(s) completed successfully.'
Now how do I get my answer ?

 
Posted on 09-18-18 2:41 PM     [Snapshot: 141]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

The skript provided by 'Dev_', creates SP and then you have to execute SP with input value sto get results..

try below skript- ( 1 quick search gave me this....), just run in each database or you can add code to run in all DB at once...

But depending upon the size of DB and data in table, this will take some time

@stylish - I have used once in the past...We had a process that we used to synch data from multiple tables between two or more sources. tables were divide into groups. One group kept failing complaining about data type and just gave the value out. Since this was in early stage of process, we didn't have enough error handling to point out which tables and
or columns. We knew list of tables from group.
Or it can also used in cases where one or some value in ur DB needs to be updated by new value (because old value was no longer in use...I know different way to handle it with FK relation but if that was not option then..) . You can either search each tables or columns one at a time, or do search on all tables and columns at once. Create Update skripts at once based on your results output and do mass replacements.
----------------------------------------------------------------------------------------
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'


-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = '
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%',')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = '
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL

BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ' + @TableName + '.' + @ColumnName + ', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results


link here -

http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated/


Last edited: 18-Sep-18 02:43 PM

 
Posted on 09-19-18 11:28 AM     [Snapshot: 313]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 
 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 90 days
Recommended Popular Threads Controvertial Threads
TRUMP 2016!!! Here is why?
Returning to Nepal
Mnang, Mustang Treking and Our Engagement
Wedding Costs in Nepal ? Party, Band Baaja !!!!???
वाह ! मन्त्री ज्यु वाह !
Four Characters and My Funeral
ICE RAID
TPS FEDERAL NOTICE
हामीहरुले २० जना केटिहरुसंग सेक्स गर्नै पर्छ रे नी !
Check out HOW TO : workout Videos
coding at age of 45
6 ÷ 2(2 + 1)=
Need some serious suggestion on Traveling to Nepal on Advance Parole
LAX to Guangzhou to Nepal - CS Air
वा अमेरिका !! थू अमेरिका !! दलदल अमेरिका
लिङ्ग को आकर कत्रो?
Anyone renewed their passport through DC embassy lately?
How long does it take to get I485 approved?
बिजया दशमीको मंगलमय शुभकामना !
भिनाजुको स्वेटर
Funny jokes
TRUMP 2016!!! Here is why?
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters