Need help to search for extra space in all tables.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help to search for extra space in all tables.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-31 : 13:08:04

Hi,

I try to search for data which has some extra spaces in ALL TABLES.
Does any one know if there a tool that can do that or the scripts can return all the data (see desire results below).
Thank you in advance. SQL2008.

IF OBJECT_ID('Tempdb.dbo.#T1', 'u') IS NOT NULL
DROP TABLE #T1
GO
CREATE TABLE #T1
(
FullName VARCHAR(30)
)
GO
INSERT INTO #T1 VALUES ('Joe Smith')
INSERT INTO #T1 VALUES ('April Smith ')
INSERT INTO #T1 VALUES ('Peter Chen')
INSERT INTO #T1 VALUES ('Kristine Nguyen')
GO

IF OBJECT_ID('Tempdb.dbo.#T2', 'u') IS NOT NULL
DROP TABLE #T2
GO
CREATE TABLE #T2
(
FullName VARCHAR(30)
)
GO
INSERT INTO #T2 VALUES (' John Smith')
INSERT INTO #T2 VALUES ('Lisa Tran ')
INSERT INTO #T2 VALUES ('Nicole Ngo')
INSERT INTO #T2 VALUES ('Brandon Lee')

GO

SELECT *
FROM #T1;
GO

SELECT *
FROM #T2;
GO

-- Desire results.

FullName TableName
---------------- ---------
Joe Smith #T1 (2 spaces in between)
April Smith #T1 (space after Smith)

John Smith #T2 (space in front)
Lisa Tran #T2 (space after Tran)
Nicole Ngo #T2 (2 spaces in between)

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-08-31 : 16:30:32

Here's one piece of the puzzle, although a small one:


IF OBJECT_ID('Tempdb.dbo.#T1', 'u') IS NOT NULL
DROP TABLE #T1
GO
CREATE TABLE #T1
(
FullName VARCHAR(30)
)
GO
INSERT INTO #T1 VALUES ('Joe  Smith')
INSERT INTO #T1 VALUES ('April Smith ')
INSERT INTO #T1 VALUES ('Peter Chen')
INSERT INTO #T1 VALUES ('Kristine Nguyen')
GO


IF OBJECT_ID('Tempdb.dbo.#T2', 'u') IS NOT NULL
DROP TABLE #T2
GO
CREATE TABLE #T2
(
FullName VARCHAR(30)
)
GO
INSERT INTO #T2 VALUES (' John Smith')
INSERT INTO #T2 VALUES ('Lisa Tran ')
INSERT INTO #T2 VALUES ('Nicole  Ngo')
INSERT INTO #T2 VALUES ('Brandon Lee')

GO

WITH n
AS
(
SELECT		n.number
FROM		master..spt_values 
AS			n
WITH		(NOLOCK)
WHERE		n.[type] = 'P' 
)
select a.FullName
FROM #T1 a
JOIN n ON n.number between 1 and LEN(a.FullName+':')-1
where substring(a.fullname,n.number,1) = ' '
group by a.FullName
having COUNT(*) > 1

union all 

select a.FullName
FROM #T2 a
JOIN n ON n.number between 1 and LEN(a.FullName+':')-1
where substring(a.fullname,n.number,1) = ' '
group by a.FullName
having COUNT(*) > 1

/*
FullName
------------
April Smith 
Joe  Smith
 John Smith
Lisa Tran 
Nicole  Ngo
--*/

Just as an example, you could use INFORMATION_SCHEMA.COLUMNS to find all the tables and columns matching a specific name or data type (varchar) and build the sql string like this:


select 'union all select a.[' + COLUMN_NAME + ']  FROM ' + TABLE_NAME + ' a JOIN n ON n.number between 1 and LEN(a.[' + COLUMN_NAME + ']+'':'')-1 where substring(a.[' + COLUMN_NAME + '],n.number,1) = '' '' group by a.[' + COLUMN_NAME + '] having COUNT(*) > 1'
from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar'

Again, just an example. You probably want to make a function and then build the sql string off the function.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-01 : 05:54:41

See if this also helps
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources