How to Trim all the Spaces from all Character Type Field of the Tables of the Database

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

                  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
Ebook Download
View all
Learn
View all