Administrators Nathan Posted February 14, 2012 Administrators Share Posted February 14, 2012 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 Quote Link to comment Share on other sites More sharing options...
haye55987 Posted February 22, 2012 Share Posted February 22, 2012 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.