Jump to content

Search All Data in All Tables


Nathan

Recommended Posts

  • Administrators

The following script will allow you to search all the data in all the tables within a certain database.

 

This is useful for poorly labeled databases or when you have no data diagram to work off.

 

If you know a unique piece of data that you see within the application itself you can then query the database for it.

 

However, I wouldn't recommend running it on a production server during business hours.

 

All you need to do is replace "DatabaseName" with you database name. Then execute it and it will be written into your database.

 

Then just call it with the string of text you are looking for example: SearchAllTables '%Important Data%'

 

USE [DATABASENAME]
GO
/****** Object:  StoredProcedure [dbo].[searchAllTables]	Script Date: 07/30/2010 10:37:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[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

Link to comment
Share on other sites

Just wanted to point out...as the original poster did...this is a very expensive operation. I would not recommend running this on a production server in an environment where you do not know the impacts of using memory, bandwidth. On a home server or something you run a small instance of this is a useful tool to find data...

 

If anyone is interested on converting this query into something an Oracle database can use please let me know and I can write up something similar.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...