SQL 2005 search

This will list all the tables & its columns in SQL Server

Raj

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION

Select allOpen in new window

I dont want to list them, i want to be able to search for a word within all the tables and colums?

Run this script. You will get a bunch of queries. Copy it from results window and paste it in query window and run all together.

Raj

DECLARE @TABLENAME VARCHAR(50), @COLUMNNAME VARCHAR(50)
DECLARE @SEARCHSTRING VARCHAR(50), @SQL NVARCHAR(4000)
SET @SEARCHSTRING = 'test'
DECLARE CCURSOR CURSOR FOR 
	select TABLE_NAME, COLUMN_NAME
	from INFORMATION_SCHEMA.COLUMNS
	order by TABLE_NAME, ORDINAL_POSITION
OPEN CCURSOR
FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME

WHILE @@FETCH_STATUS=0
BEGIN
	SET @SQL = ' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' = ''' + @SEARCHSTRING + ''''
	PRINT @SQL
	FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME
END

CLOSE CCURSOR
DEALLOCATE CCURSOR

Select allOpen in new window

1. First run it against the database you need to check
2. Set the string which you want to search in
   SET @SEARCHSTRING = 'test'

Let me know

Raj

I get these errors

The data types text and varchar are incompatible in the equal to operator.

Did you got the scripts generated by my script ?

I assume the error is while executing the generated scripts, right?

There may be a 'text' datatype column in anyone of the table - that cause this crash. Because we cannot compare a text field with varchar data type..

I will modify the query to accomodate this condition and  will be back

Raj

>> The data types text and varchar are incompatible in the equal to operator

Updated script with its fix. Let me know...

Raj

DECLARE @TABLENAME VARCHAR(50), @COLUMNNAME VARCHAR(50) , @DATATYPE VARCHAR(50)
DECLARE @SEARCHSTRING VARCHAR(50), @SQL NVARCHAR(4000) 
SET @SEARCHSTRING = 'test' 
DECLARE CCURSOR CURSOR FOR  
        select TABLE_NAME, COLUMN_NAME, DATA_TYPE 
        from INFORMATION_SCHEMA.COLUMNS 
        order by TABLE_NAME, ORDINAL_POSITION 
OPEN CCURSOR 
FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE
 
WHILE @@FETCH_STATUS=0 
BEGIN 
	IF @DATATYPE = 'text' 
        SET @SQL = ' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''' + @SEARCHSTRING + '''' 
	ELSE
        SET @SQL = ' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' = ''' + @SEARCHSTRING + '''' 

        PRINT @SQL 
        FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE
END 
 
CLOSE CCURSOR 
DEALLOCATE CCURSOR

Select allOpen in new window

Many thanks. Will let you asap

This is what i get now?

Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.

Since there may be various datatypes in your tables, we need to handle some of them in different way. (As done for 'text')

Will be back

Raj

Hi monarchit,

1. if you are searching for word, means we have to consider only text fields like varchar, char, nvarchar, text.. Then I can exclude these datatypes that cause crash.

Can I limit it to word search ? It will also accept integers also. So don't worry about that

Let me know


2. Post the result of this query in your targetted database
        select DISTINCT DATA_TYPE  
        from INFORMATION_SCHEMA.COLUMNS  

Raj

In this particular search I am only looking for numbers?

I will post the results back.

Thank you

Hi monarchit,

I have updated my query with some fixes.

1. Excluded some datatypes from search. (check the script) - Excluded characters also. Now you can do integer search.

2. Given [ ] for table names and column names, as there are chance that error can occur while executing it
eg: SELECT FROM YourTable WHERE ORDER = 1
This will cause error, as ORDER is an internal keyword. So corrected query is
eg: SELECT FROM YourTable WHERE [ORDER] = 1


One big thing you have to take care is that, here we are searching in most of the columns in all tables. So it may took time depending on the size of the data, indexes, etc. etc...

Better to execute some queries at once. I mean 10 queries at one time, then next 10...

After you post the results of this query, I can double check whether all integer data types are included in my script.
  select DISTINCT DATA_TYPE  
        from INFORMATION_SCHEMA.COLUMNS  


Hope this helps. Let me know
Raj

DECLARE @TABLENAME VARCHAR(50), @COLUMNNAME VARCHAR(50) , @DATATYPE VARCHAR(50) 
DECLARE @SEARCHSTRING VARCHAR(50), @SQL NVARCHAR(4000)  
SET @SEARCHSTRING = 100.10
DECLARE CCURSOR CURSOR FOR   
        select TABLE_NAME, COLUMN_NAME, DATA_TYPE  
        from INFORMATION_SCHEMA.COLUMNS  
        order by TABLE_NAME, ORDINAL_POSITION  
OPEN CCURSOR  
FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE 
  
WHILE @@FETCH_STATUS=0  
BEGIN  
		IF @DATATYPE = 'image' OR @DATATYPE = 'varbinary' OR @DATATYPE = 'datetime' OR
			@DATATYPE = 'binary' OR @DATATYPE = 'smalldatetime' OR @DATATYPE = 'real' OR
			@DATATYPE = 'bigint' OR @DATATYPE = 'bit' 
			-- EXCLUDED THESE DATATYPES
			SET @SEARCHSTRING = @SEARCHSTRING

		ELSE IF @DATATYPE = 'text' OR @DATATYPE = 'ntext' OR @DATATYPE = 'varchar' OR
				@DATATYPE = 'nvarchar' OR @DATATYPE = 'char'

			SET @SEARCHSTRING = @SEARCHSTRING
			-- SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''' + @SEARCHSTRING + ''''  
        ELSE  
			SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] = ''' + @SEARCHSTRING + ''''  
 
        PRINT @SQL  

        FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE 
END  
  
CLOSE CCURSOR  
DEALLOCATE CCURSOR

Select allOpen in new window

Jump back on tomorrow and give you a shout back
cheers

timestamp
image
money
int
decimal
text
smallint
datetime
varchar
binary
tinyint
smalldatetime
float
char
bigint
bit
ntext
nvarchar

Here is updated query. Try this

Raj

DECLARE @TABLENAME VARCHAR(50), @COLUMNNAME VARCHAR(50) , @DATATYPE VARCHAR(50)  
DECLARE @SEARCHSTRING VARCHAR(50), @SQL NVARCHAR(4000)   
SET @SEARCHSTRING = 100.10 
DECLARE CCURSOR CURSOR FOR    
        select TABLE_NAME, COLUMN_NAME, DATA_TYPE   
        from INFORMATION_SCHEMA.COLUMNS   
        order by TABLE_NAME, ORDINAL_POSITION   
OPEN CCURSOR   
FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE  
   
WHILE @@FETCH_STATUS=0   
BEGIN   
		-- OTHER DATATYPE SEARCH ---[DISABLED]
        IF @DATATYPE = 'image' OR @DATATYPE = 'varbinary' OR @DATATYPE = 'datetime' OR 
                @DATATYPE = 'binary' OR @DATATYPE = 'smalldatetime' OR 
                 @DATATYPE = 'timestamp' 
                -- EXCLUDED THESE DATATYPES 
                SET @SEARCHSTRING = @SEARCHSTRING 

		-- STRING SEARCH  ---[DISABLED]
        ELSE IF @DATATYPE = 'text' OR @DATATYPE = 'ntext' OR @DATATYPE = 'varchar' OR 
                        @DATATYPE = 'nvarchar' OR @DATATYPE = 'char' 

                SET @SEARCHSTRING = @SEARCHSTRING 
                -- SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''' + @SEARCHSTRING + ''''   
        
		ELSE   -- NUMBER SEARCH
                SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] = ''' + @SEARCHSTRING + ''''   
  
        PRINT @SQL   
 
        FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE  
END   
   
CLOSE CCURSOR   
DEALLOCATE CCURSOR

Select allOpen in new window

he conversion of the varchar value '3756897' overflowed an INT1 column. Use a larger integer column.
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

1. What data you are trying to search ?

2. Check the exact query that cause this crash. And let me know that datatype of the column on which you are searching in that query ?

For eg:
SELECT * FROM YourTable WHERE ID = '234234'

I neeed to know the datatype of the field 'ID' in 'YourTable'

Raj

I dont actually know because i run the query on everything that your script outputted.

Looks like they are int and tinyint

i did find a smallint and a money also

This error may occur due to conflit in datatype's allowed limit and searching data...

1. What string you entered to search ?

2. Double-click on the error message. it will highlight the query that cause that error. then check the datatype of the field after WHERE condition.

Raj

Msg 244, Level 16, State 1, Line 17
The conversion of the varchar value '3756897' overflowed an INT1 column. Use a larger integer column.

 this was tinyint???

Ya! Got that error. Fixed it. Try this

Raj

DECLARE @TABLENAME VARCHAR(50), @COLUMNNAME VARCHAR(50) , @DATATYPE VARCHAR(50)   
DECLARE @SEARCHSTRING VARCHAR(50), @SQL NVARCHAR(4000)    
SET @SEARCHSTRING = 100.10  
DECLARE CCURSOR CURSOR FOR     
        select TABLE_NAME, COLUMN_NAME, DATA_TYPE    
        from INFORMATION_SCHEMA.COLUMNS    
        order by TABLE_NAME, ORDINAL_POSITION    
OPEN CCURSOR    
FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE   
    
WHILE @@FETCH_STATUS=0    
BEGIN    
                -- OTHER DATATYPE SEARCH ---[DISABLED] 
        IF @DATATYPE = 'image' OR @DATATYPE = 'varbinary' OR @DATATYPE = 'datetime' OR  
                @DATATYPE = 'binary' OR @DATATYPE = 'smalldatetime' OR  
                 @DATATYPE = 'timestamp'  
                -- EXCLUDED THESE DATATYPES  
                SET @SEARCHSTRING = @SEARCHSTRING  
 
                -- STRING SEARCH  ---[DISABLED] 
        ELSE IF @DATATYPE = 'text' OR @DATATYPE = 'ntext' OR @DATATYPE = 'varchar' OR  
                        @DATATYPE = 'nvarchar' OR @DATATYPE = 'char'  
 
                SET @SEARCHSTRING = @SEARCHSTRING  
                -- SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''' + @SEARCHSTRING + ''''    
         
                ELSE   -- NUMBER SEARCH 
					IF isnumeric(@SEARCHSTRING) = 0
						SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] = ''' + @SEARCHSTRING + ''''    
					ELSE
						SET @SQL = ' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] = ' + @SEARCHSTRING 
   
        PRINT @SQL    
  
        FETCH NEXT FROM CCURSOR INTO @TABLENAME, @COLUMNNAME , @DATATYPE   
END    
    
CLOSE CCURSOR    
DEALLOCATE CCURSOR

Select allOpen in new window

it was going well and then got this:

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

I think, in the result grid only results of maximum of 100 queries can be displayed.
Execute 100 queries each

Raj

monarchit,

Have a try with this Stored Procedure. You just need to pass the value to search to it. It is supposed to search in all tables, in all columns for that value and returns the tablename & column name that contains that value.

I am not the creator of this Stored Procedure :)
I got this Stored Procedure from
https://www.experts-exchange.com/questions/25785576/Search-for-text.html?cid=1576

I have tested it with string & integer values. Working :)

Execute like
SearchAllTables 1000 -- number
SearchAllTables 'test' -- string

Raj

CREATE PROC 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

Select allOpen in new window

I dont get where in the script I am to put the number i am looking for?

1. Create that Stored Procedure in your database.

2. Use this Stored Procedure to search for the number you want.
Remember no need to alter the Stored Procedure.

If you want to search for the number 100,
Execute like
SearchAllTables 1000

Hope this helps
Raj

Once I have saved it, how do I search using that procedure?

Once you created that Stored Procedure in your database, open a new query window and type like this

SearchAllTables 1000

After typing press F5 key to execute it.

This is the case when you are searching for the number 1000 in your database.

Raj

I could do with one more bit of help. How do i fill in the below? I cant view any of  my other stored procedures.

-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
      -- Add the parameters for the stored procedure here
      <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
      <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Hi monarchit,

What you are trying to do ?

Raj

You said to save that as a stored procedure? How do I go about doing that?