How do I search across all columns in all tables in SQL?
The development practice at Webapper does a fair bit of “version 2″ system development. Our customer has an existing application, built maybe in ColdFusion, maybe in PHP or another scripting language. Maybe in Access forms, maybe as a desktop application. We’ll use it as a prototype for creating a new webified application.
One part of these jobs is tracing how the old application works. Since there is never any documentation, and the source code is often too scary to look at (or no longer even available), it can be hard to find out the business rules underlying the software.
I’m working on such a project these days, and needed to find out where a particular string is being stored in the old database. The database is not normalized, there are no foreign key constraints, and there are inconsistent key names (like users.user_id and records.u_id as pkey and fkey, respectively).
I needed to find where in the database a particular string was being stored. I hunted in the most obvious tables and columns, but there are about 100 tables, and about 500 megs of data (lots of rows). I needed a SQL script to hunt down a particular keyword.
Google found Doug Hughes’s stored proc, which I remember having used with success in the past, but today it threw a ton of errors and came up empty handed. A bit more Googling turned up Vyas’s better stored proc. It worked like a charm. Hope this helps you!
LOL… Nat that’s my day job in a nutshell too. Aren’t Access DB’s a hoot?
Comment by Jen — February 26, 2007 @ 12:00 am
The first thing I do when I get an Access DB is run the Upsizing Wizard on it so I can work with it in MSSQL. I simply can’t stand Access!
Comment by Nat Papovich — February 27, 2007 @ 12:00 am
how big is the db? you could always do an export as sql insert statements and just search the text file…. kinda retro but simple
Comment by zac spitzer — February 27, 2007 @ 12:00 am
It was 500 megs, which I think would produce approximately one gajillion lines of insert statements. The sproc I mentioned in the post takes about 10 seconds to return what I’m looking for. Good idea though, especially if I’m ever working with a non-Access/MSSQL database.
Comment by Nat Papovich — February 28, 2007 @ 12:00 am
Hello,
With the system tables, there is a better way to code this stored procedure. I spent an hour today and came up with this development for you and also added as parameter the database, because if you have 100 databases on your server, you do not want to copy the stored procedure but rather have a repository (centralised database for this).
Here is the code, tested on Sql server 2008.
Enjoy!
– script
Use
GO
CREATE Tool Authorization dbo
GO
CREATE PROCEDURE Tool.SearchAllTables
(
@DbName sysname,
@SearchStr nvarchar(100)
)
AS
BEGIN
– COPYRIGHT Clement Huge 2010-07-17: search specific string within a specific database data
– sample: EXEC SearchAllTables ”, ‘data’
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @SearchStmt nvarchar(MAX), @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
CREATE TABLE #Stmt (Stmt varchar(max))
SET @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””)
SET @SearchStmt =
‘
SELECT ”SELECT ””” + S.name + ”.” + Ob.name + ”.” + Co.Name + ”””, LEFT(” + Co.Name + ”, 3630) FROM ['+ @DbName + '].['' + S.name + ''].['' + Ob.name + ''] WITH (READUNCOMMITTED) WHERE ” + Co.Name + ” LIKE ”’ + @SearchStr2 + ””’
FROM ['+ @DbName + '].sys.syscolumns Co
JOIN ['+ @DbName + '].sys.tables Ob ON Co.id = Ob.Object_Id
JOIN ['+ @DbName + '].sys.schemas S ON S.schema_id = Ob.schema_id
WHERE Co.xtype IN (select xtype from sys.systypes WHERE name LIKE ”%char%”)
‘
INSERT INTO #Stmt
EXEC ( @SearchStmt )
DECLARE Cur_SearchList CURSOR FOR
SELECT Stmt FROM #Stmt
OPEN Cur_SearchList
FETCH NEXT FROM Cur_SearchList INTO @SearchStmt
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Results
EXEC ( @SearchStmt )
FETCH NEXT FROM Cur_SearchList INTO @SearchStmt
END
CLOSE Cur_SearchList
DEALLOCATE Cur_SearchList
SELECT ColumnName, ColumnValue FROM #Results
END
Comment by Clement Huge — July 17, 2010 @ 3:49 pm