Search all Tables in DB for a String but using Postgres - What is the difference

• Oscar_Boots

  Old Hand

  Points: 362

  More actions

  May 9, 2021 at 9:05 am

  #3881105

  Hi Forum,

  I want to search a Database for a String value and I have found a script that works. Only thing is, I am using PostGres & am not very experienced in the difference between the two.

  The script is below so if someone can let me know what needs to change to get it to work in PostGres, it would help a lot. Thanks

  Thanks to Reto Egeter for this script.

  /* Reto Egeter, fullparam.wordpress.com */
  
  DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
  SET @SearchStrColumnValue = '%plains%' /* use LIKE syntax */
  SET @FullRowResult = 1
  SET @FullRowResultRows = 3
  SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
  SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
  SET @SearchStrInXML = 0 /* Searching XML data may be slow */
  
  IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
  CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))
  
  SET NOCOUNT ON
  
  DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
  SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
  DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
  
  WHILE @TableName IS NOT NULL
  BEGIN
      SET @TableName =
      (
          SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
          FROM    INFORMATION_SCHEMA.TABLES
          WHERE       TABLE_TYPE = 'BASE TABLE'
              AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
              AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
              AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
      )
  IF @TableName IS NOT NULL
      BEGIN
          DECLARE @sql VARCHAR(MAX)
          SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
                  FROM    INFORMATION_SCHEMA.COLUMNS
                  WHERE       TABLE_SCHEMA    = PARSENAME(''' + @TableName + ''', 2)
                  AND TABLE_NAME  = PARSENAME(''' + @TableName + ''', 1)
                  AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
                  AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
          INSERT INTO @ColumnNameTable
          EXEC (@sql)
          WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
          BEGIN
              PRINT @ColumnName
              SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
              SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
              WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
              ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
                      FROM ' + @TableName + ' (NOLOCK) ' +
                      ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
                      WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                      ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
              INSERT INTO #Results
              EXEC(@sql)
              IF @@ROWCOUNT > 0 IF @FullRowResult = 1
              BEGIN
  SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
                      ' FROM ' + @TableName + ' (NOLOCK) ' +
                      ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
                      WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                      ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
                  EXEC(@sql)
              END
              DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
          END
      END
  END
  SET NOCOUNT OFF
  
  SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
  GROUP BY TableName, ColumnName, ColumnValue, ColumnType
  

   

• Site Owners

  SSC Guru

  Points: 80325

  More actions

  May 10, 2021 at 9:10 am

  #3881369

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.