How to trim all the spaces
from all charcter type field of the tables of the database.
CREATE
PROCEDURE TRIM_DATABASE
(@TableName
Varchar(128)='')
AS
DECLARE
@strSQL VARCHAR(2000)
Declare
@Tname NVARCHAR(128)
--Table Name
DECLARE
@FName NVARCHAR(128)
--Field Name
DECLARE
@FType NVARCHAR(128)
--Filed Type
IF
@TableName=''
DECLARE C1 CURSOR
FOR SELECT
[Name] FROM Sysobjects
WHERE Xtype='U'
AND [NAME] LIKE
'%MAST%'
UNION ALL
SELECT [Name] FROM
SysObjects WHERE
Xtype='U'
AND [NAME] NOT
LIKE '%MAST%'
ELSE
DECLARE C1 CURSOR
FOR SELECT
[Name] FROM Sysobjects
WHERE Xtype='U'
AND [NAME] LIKE
@TableName
OPEN C1
FETCH NEXT
FROM C1 INTO @Tname
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE C2 CURSOR
FOR SELECT
RTRIM(B.[NAME])
AS Fname,RTRIM(C.[NAME])
AS FType
FROM SYSOBJECTS
A, SYSCOLUMNS B,
SYSTYPES C
WHERE A.[ID]=B.[ID]
AND B.XUSERTYPE=C.XUSERTYPE
AND RTRIM(A.[NAME])
LIKE @Tname
OPEN C2
FETCH NEXT
FROM C2 INTO @FNAME,@FTYPE
WHILE @@FETCH_STATUS=0
BEGIN
IF @FTYPE='NVARCHAR'
OR @FTYPE='VARCHAR'
BEGIN
SET @STRSQL
= 'UPDATE '
+ @Tname +
' SET ' + @FName
+ '='
+ 'RTRIM(LTRIM('
+ @FName +
'))'
EXEC(@strSQL)
END
FETCH NEXT
FROM C2 INTO @FName,@FTypeEND
CLOSE
C2DEALLOCATE
C2
FETCH
NEXT FROM C1
INTO @TnameEND
CLOSE
C1
DEALLOCATE
C