CREATE PROCEDURE TRIM_DATABASE (@TableName Varchar(128)='')
ASDECLARE @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

Join the conversation! Your thoughts help the community grow.