Applying T-SQL...Back to Basics

Question

IM very new to TSQL and am getting my head around stored proceedures etc.

I am using the Code to find a value within one table in my data base, but im not too sure how i would use this code...

Do i need to replace all @* with my relevant table or column name or simply compy paste and execute

Thanks for the help

How do I find a value anywhere in a SQL Server Database?

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 and SQL Server 2000 
-- Date modified: 28th July 2002 22:50 GMT  
DECLARE @Results 
TABLE(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 (free) SSMS Tools Pack addin has functionality to search all tables in a DB. — Martin Smith, May 28 '12 at 10:28

score 1 · Answer 1 · answered May 28 '12 at 12:15

Well, to answer your question:

First you have to copy the code to a query window an run it. That will create the stored procedure.

Now you can call the stored procedute by calling:

EXEC SearchAllTables 'a string of your choice'

Note that you will only get hits from text columns (like 'char', 'varchar', 'nchar', 'nvarchar').

Applying T-SQL...Back to Basics

1 Answers1