How to search all columns of all tables in a database for a keyword

Hi,
I have lost the password to an application that it's database resides on SQL. I was wondering if I can look for the username and password in 1 of the tables in the SQL and then change the password.

My proble is that i have more thab 300 tables in the database. is there a way to search all columns of all tables in a database for a keyword?
SharonessAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Hello Sharoness,

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROC SearchAllTables
      @SearchStr nvarchar(100)
AS
BEGIN


      CREATE TABLE #Results (i int identity, TableName varchar(450), 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', 'uniqueidentifier')
                              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
                        )
                        IF @@ROWCOUNT = 0
                              DELETE FROM #results where i = SCOPE_IDENTITY()-1
                  END
            END      
      END

      SELECT DISTINCT TableName, ColumnName, ColumnValue FROM #Results
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



Aneesh R
0
 
SharonessAuthor Commented:
How do I use it?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
in order to search for a keyword, after running the above  (Please replace 'Alter procedure' with 'CREATE PROCEDURE'  )  you have run this on a new query window

exec  SearchAllTables  'keywordToSearch'

0
All Courses

From novice to tech pro — start learning today.