Search all columns for a string?

Transact-SQL
1

Hello,

I have some output which is sent to a third party in the form of a pipe delimited text file and, up until now, everything has been fine.

However, we upgraded our database software and some of the field types have changed. To cut a long story short, some fields now contain a pipe character. which means the structure of the output file is not as it should be.

Is there some code I can run to see which columns contain a pipe character?

4

Hey Ronaldinho

Is this for 1 table or all tables in the db?

5

Just one table :slight_smile:

6

I use this old technique. I think it still works for SQL Server 2019
USE [Your DB here]

CREATE PROC [dbo].[spSearchAllTables]
(
@SearchStr varchar(100)
)
AS

-- Must run this within the database you intend to search
-- To execute: exec dbo.spSearchAllTables 'stringToSearchFor'
BEGIN
CREATE TABLE #Results (ColumnName varchar(370), ColumnValue varchar(3630))
SET NOCOUNT ON
DECLARE @TableName varchar(256), @ColumnName varchar(128), @SearchStr2 varchar(100)
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', 'int')
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

1 Like
7

please post the table's schema ?

create table fezuado(
id int
)
--etc
1 Like
8

What are you going to do to correct the problem? And is that the right approach?

You obviously have a set of code that is used to output the data for that text file, and a copy of the text file that is incorrect. You should be able to view the text file and be able to identify possible columns causing the issue - and from there you can manually run the query to identify the actual columns.

Next step is to determine how to address the issue. If the developers are now storing multiple values in a single column (really bad idea - btw), then you need to figure out a solution. Do you send just the first one - or all of them with a different delimiter - or do you unpivot that column and send multiple rows - or something else?

You already have a starting point - work with that to identify the offending column(s).

1 Like