Searching in all tables and columns of a database
I was forced recently to do some maintenance and bug fixing on an aging .NET-based CMS.
Most of the problems were in the SQL Server database, with lots of corrupted entries. The most frustrating thing however was that at times I didn’t even know where to find the entries. Try finding some specific strings in a database with a hundred tables, each with many columns (poorly named, obviously) and tens of thousands of records – it’s like finding the proverbial needle in the haystack.
You may remember a similar issue we had a few months ago, where we wrote a stored procedure to search & replace all occurences of a string in a table column. That was easy enough since we knew were to look.
The cool thing (even though it’s daunting at first) about SQL server is that you can obtain any information about the databases using SQL queries. After having a look at System Databases - master and its Views, it became a little clearer how to do it. Even better, I discovered that someone else had the same problem as myself, sparing me the need to reinvent the wheel.
The full source code of the stored procedure is here:
T-SQL:
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: http://vyaskn.tripod.com | |
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL server 2005 | |
-- 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') | |
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 |
The original article can be found here.
PermalinkNo feedback yet
Comment feed for this postLeave a comment
Aggregated by MXNA
