Find string in every DB of an instance

Microsoft SQL
Post Reply
dzcadii
Posts: 94
Joined: Sun Mar 01, 2015 4:36 am

Find string in every DB of an instance

Post by dzcadii » Sun Mar 01, 2015 11:16 pm

Here is a way to find a string value in every database in an instance.
This returns:
DatabaseName, Column, Value

Use this to create a stored procedure in the database

Code: Select all

-- uncomment the following line to create the sproc
--CREATE PROC FindStringInDb
--uncomment the following line to edit the sproc
--ALTER PROC [dbo].[FindStringInDb]
(
      @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 DB_NAME() AS DatabaseName, ColumnName, ColumnValue FROM #Results
END

Use this query to call the sproc

Code: Select all

--begin
--select name, database_id, create_date
--from sys.databases;
--end

begin
declare @stringToLookFor varchar(255) = 'The string that you are looking for'
declare @dbId int = 1
declare @dbCount int = (select COUNT(name) from sys.databases)
declare @dbNames varchar(100)
declare @qStat varchar(MAX)

while @dbId != @dbCount
	begin
		set @dbNames = (SELECT name from sys.databases where database_id = @dbId)
		set @qStat = @dbNames + '.dbo.FindStringInDb'
		declare @sysDbs bit = 0
                --Exclusions
		if(@dbNames = 'master' or @dbNames = 'model' or @dbNames = 'msdb' or @dbNames = 'tempdb' or @dbNames LIKE 'ReportServer%')
			set @sysDbs = 1
		else
			begin
				set @sysDbs = 0
				exec @qStat @stringToLookFor				
			end
		set @dbId = @dbId + 1
	end
end

%-( PS: I'm not getting into the whole pointer or not or set or not. %-(

Post Reply