[Show all top banners]

phone
Replies to this thread:

More by phone
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Refresh page to view new replies
 How to search column using value in sql server
[VIEWED 8263 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 30 days
Recommended Popular Threads Controvertial Threads
What are your first memories of when Nepal Television Began?
nrn citizenship
Send Parcels from USA to Nepal & Worldwide.
Why is every youths leaving Nepal? Why are not youths entering politics and serving the country, starting business etc?
TPS Work Permit/How long your took?
महँगो अण्डाको पिकल्प : कुखुरा र खोर भाडामा लिने
Travelling on TPS advance travel document to different country...
Informatica consultancy share
काेराेना सङ्क्रमणबाट बच्न Immunity बढाउन के के खाने ?How to increase immunity against COVID - 19?
TPS Renewal Reregistration
Nepali doctors future black or white usa ?
हेर अमेरिकामा नेपालीहरुको बेज्जत
Thank you for the sajha policy
Are you ready to know the truth?
मन भित्र को पत्रै पत्र!
चितवनको होस्टलमा १३ वर्षीया शालिन पोखरेल झुण्डिएको अवस्था - बलात्कार पछि हत्याको शंका - होस्टेलहरु असुरक्षित
Another Song Playing In My Mind
Does the 180 day auto extension apply for TPS?
Travelling to Nepal - TPS AP- PASSPORT
WHAT DO YOU GUYS THINK ABOUT THIS?
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