August 26

MSSQL: Find specific data in any table of a database

MSSQL: Find specific data in any table of a database

 

NOTE:  RUN USE MASTER INDEPENDENTLY OF REMAINDER OF QUERY.  This query runs slowing and takes a lot of server resource.

USE MASTER

CREATE PROCEDURE SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

By:  Narayana Vyas Kondreddi.

— Purpose: To search all columns of all tables for a given search string

 

Tested procedure:

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

 

 

NEXT RUN

 

 

Use names

 

EXEC SearchAllTables ‘nighthawk%’
GO


Copyright 2021. All rights reserved.

Posted August 26, 2011 by Timothy Conrad in category "Databases

About the Author

If I were to describe myself with one word it would be, creative. I am interested in almost everything which keeps me rather busy. Here you will find some of my technical musings. Securely email me using - PGP: 4CB8 91EB 0C0A A530 3BE9 6D76 B076 96F1 6135 0A1B