Share via

OpsMgr 2007: How to search the entire database for a string value or GUID

  • Article

Here is an older tip but it's one we use (or some variation) quite a bit in support.  This originally came from Narayana Vyas Kondreddi at https://vyaskn.tripod.com/search_all_columns_in_all_tables.htm so we can't take all the credit.  If you need to find a value in a table but you're not sure where to look, this can be a life saver:

========

Issue: Sometimes it may be necessary to find a certain value that could be stored in a table in a database. Often times it may be difficult to find such a value due to the amount of tables and the uncertainty of the location. To help locate items you are wanting to find, the following two statements can be run in SQL Server Management Studio:

Resolution: There are 2 parts for the search to work. The first creates a new stored procedure named dbo.SearchAllTables in the database that you are running it on. When the stored procedure runs it creates a new table to store the results of your search.  The second runs the stored procedure and looks for the value that you enter in the ' '.

-----  #1 --This is to create a procedure to search all text in all tables in the database

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

-- 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: <https://vyaskn.tripod.com>
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 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', 'uniqueidentifier')
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
)

--Print
@TableName
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END

----- #2 --This is to execute to procedure to find the value in all tables

EXEC SearchAllTables 'Value Here'
GO

Replace the Value Here with what you are wanting to locate. For example, if you want to find a server named ServerJoe you would enter:

EXEC SearchAllTables 'ServerJoe'
GO

-----

Enjoy!

J.C. Hornbeck | Manageability Knowledge Engineer

Comments