CODEWITHSUNDEEP
sqlsql-server-2005data-cleaning
Hell.Bent
Hell.Bent

Reputation: 1677

Return all columns with bad data SQL Server 2005 Query

I have 15 SQL Server tables, each with about 50 columns.

Some of these columns have rows that contain quotes, commas and tabs.

I have a function that removes all of these from the row given the column name, but I don't know which columns have the issue.

I'd like a SQL Server 2005 Query that can return column names that have the bad data given a table name.

Upvotes: 0

Views: 1328

Answers (1)

Chad
Chad

Reputation: 7517

There's no way you can do this without some sort of dynamic SQL. Instead of jumping through those hoops, what I usually do is write a script that generates another script.

First, the outline of a script:

declare @cols table (name varchar(500))
...
select * from @cols

Then use a query like this to generate a series of statements that will check each column for bad values:

SELECT 'IF EXISTS (SELECT * FROM [' + TABLE_NAME + ']' +
    ' WHERE [' + COLUMN_NAME + '] like ''%,%''' +
    ' or [' + COLUMN_NAME + '] like ''%''''%''' +
    ' or [' + COLUMN_NAME + '] like ''%'' + char(9) + ''%'')' +
    ' insert into @cols values (''' + COLUMN_NAME + ''')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Tablename' and DATA_TYPE in ('char','varchar')

It's just a matter of running this query, then pasting the results into your script and running that. You could also easily modify this to work on multiple tables at once.

Upvotes: 4

Related Questions