• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

SQL 2005 search

Hi,

I need to run a sql query to serach a database and all tables and colums within it. I have no idea which table or colum the word is in. Can i have the syntax for this?

Is it a stored procedure?
0
monarchit
Asked:
monarchit
  • 18
  • 17
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
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

Open in new window

0
 
monarchitAuthor Commented:
I dont want to list them, i want to be able to search for a word within all the tables and colums?
0
 
Rajkumar GsSoftware EngineerCommented:
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

Open in new window

0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
Rajkumar GsSoftware EngineerCommented:
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
0
 
monarchitAuthor Commented:
I get these errors

The data types text and varchar are incompatible in the equal to operator.
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
Rajkumar GsSoftware EngineerCommented:
>> 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

Open in new window

0
 
monarchitAuthor Commented:
Many thanks. Will let you asap
0
 
monarchitAuthor Commented:
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.
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
monarchitAuthor Commented:
In this particular search I am only looking for numbers?

I will post the results back.

Thank you
0
 
Rajkumar GsSoftware EngineerCommented:
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

Open in new window

0
 
monarchitAuthor Commented:
Jump back on tomorrow and give you a shout back
cheers
0
 
monarchitAuthor Commented:
timestamp
image
money
int
decimal
text
smallint
datetime
varchar
binary
tinyint
smalldatetime
float
char
bigint
bit
ntext
nvarchar
0
 
Rajkumar GsSoftware EngineerCommented:
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

Open in new window

0
 
monarchitAuthor Commented:
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.
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
monarchitAuthor Commented:
I dont actually know because i run the query on everything that your script outputted.
0
 
monarchitAuthor Commented:
Looks like they are int and tinyint
0
 
monarchitAuthor Commented:
i did find a smallint and a money also
0
 
Rajkumar GsSoftware EngineerCommented:
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

0
 
monarchitAuthor Commented:
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???
0
 
Rajkumar GsSoftware EngineerCommented:
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

Open in new window

0
 
monarchitAuthor Commented:
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.
0
 
Rajkumar GsSoftware EngineerCommented:
I think, in the result grid only results of maximum of 100 queries can be displayed.
Execute 100 queries each

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
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
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25785576.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

Open in new window

0
 
monarchitAuthor Commented:
I dont get where in the script I am to put the number i am looking for?
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
monarchitAuthor Commented:
Once I have saved it, how do I search using that procedure?
0
 
Rajkumar GsSoftware EngineerCommented:
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
0
 
monarchitAuthor Commented:
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
0
 
Rajkumar GsSoftware EngineerCommented:
Hi monarchit,

What you are trying to do ?

Raj
0
 
monarchitAuthor Commented:
You said to save that as a stored procedure? How do I go about doing that?
0
 
Rajkumar GsSoftware EngineerCommented:
Just copy and paste the stored procedure I posted, to SQL Server Query Window and press F5. This will create (Save) Stored Procedure. You no need to create it.

Once it is done, in a new query window, type
SearchAllTables 1000
and press F5
(to search for the number 1000)

Raj

0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 18
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now