Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db. Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 12:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:26 AM
Points: 123, Visits: 456
HI ALL,

As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.

SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?

All Help is appreciated.
Post #1232230
Posted Monday, January 9, 2012 1:10 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:57 PM
Points: 75, Visits: 215
Ivan Mohapatra (1/9/2012)
HI ALL,

As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.

SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?

All Help is appreciated.


You'll need to use dynamic SQL to query for the character columns, then in a cursor loop through those columns looking for the correct values.
Post #1232232
Posted Monday, January 9, 2012 1:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:26 AM
Points: 123, Visits: 456
hi u mean by using DMV query can u just Send me the query so that i can go through it and find the query
Post #1232235
Posted Monday, January 9, 2012 1:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 1:51 AM
Points: 49, Visits: 205
hi you may use the Len variable.

select employeeID, EmployeeName, EmployeeTask from Employees
where Len(employeedID) = 9

this will post only all employeeID whos lenght is 9 eg.(000333444)

hope this helps :)

cheers!


===============================================================




"lets do amazing" our company motto..
Post #1232238
Posted Monday, January 9, 2012 2:01 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 1:51 AM
Points: 49, Visits: 205
in addition you may use this script.. :)

you may want to run this per database lol.. haha just change the value to what you want to search lol.. you get the idea? just tweek it some more and you might be able to modify it to the one you need. this will return the table where it was located.



EXEC SearchAllTables 'Computer'

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

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')
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
END



===============================================================




"lets do amazing" our company motto..
Post #1232242
Posted Monday, January 9, 2012 2:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:26 AM
Points: 123, Visits: 456
i don'nt know the table & column
i just want to find table name and column name where data len = 9

can any one ping the script PLZ
Post #1232244
Posted Monday, January 9, 2012 2:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:26 AM
Points: 123, Visits: 456
i don't know the table name and column name So this query will not match my requirement.
Post #1232262
Posted Monday, January 9, 2012 2:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:26 AM
Points: 123, Visits: 456

mrdenny can u send me the script
Post #1232266
Posted Monday, January 9, 2012 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 12,928, Visits: 12,347
You need to find table and column names for any table that contains a numeric value that is exactly 9 digits?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1232465
Posted Monday, January 9, 2012 8:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:26 AM
Points: 123, Visits: 456
yes exactly i want all the column name and table name where value is 9 character
Post #1232474
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse