TSQLINDENTER

				
--**************************************
-- Name: TSQLINDENTER
-- Description:STORED PROCEDURE WRITTEN IN T-SQL TO INDENT A T-SQL SOURCE CODE AND SHOW IF THERE IS A DIFFERENCE BETWEEN THE NUMBER OF COMMANDS BEGIN AND COMMANDS END.
ALSO IT SHOWS HOW TO OPEN AND READ A TEXT FILE AND HOW TO WRITE A TEXT FILE IN THE HARD DISK
-- By: Aitor Solozabal Merino
--
-- Inputs:THE NAME OF THE TEXT FILE WITH THE T-SQL CODE
--
-- Returns:0 IF ALL GOES WELL
--
-- Assumes:IT IS A GOOD PRACTICE TO WRITE THE COMMANDS TO FLOW CONTROL IN THE SAME LINE IF THE PAIR BEGIN-END IS NOT USED
--
-- Side Effects:THE UTILITY WRITE A NEW TEXT FILE WITH THE CODE INDENTED WITH THE WORD 'NEW' ADDED
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1090&lngWId=5--for details.--**************************************

---------------------------------------------------------------------
-- APPLICATION : UTILITY TO INDENT T-SQL SOURCE CODE
-- AUTHOR: AITOR SOLOZABAL MERINO ( SPAIN )
-- EMAIL: aitor-3@euskalnet.net
-- TITLE: T-SQL-INDENTER
-- DESCRIPTION : PROCEDURE WRITTEN IN T-SQL TO BASIC INDENT OF SOURCE
--TRANSACT SQL CODE
-- DATE : 21/08/2006
-- RETURN VALUE : 0 SI NO HA HABIDO ERRORES
----------------------------------------------------------------------
IF OBJECT_ID('TSQLINDENTER') IS NOT NULL DROP PROCEDURE TSQLINDENTER
GO
CREATE PROCEDURE TSQLINDENTER
(
-- sample : EXECUTE TSQLINDENTER 'PROGRAM.SQL'
@NOMFIC VARCHAR(250), --'PROGRAM.SQL'
@RETURNVALUE INT=NULL OUT
)
AS
BEGIN
 -- VARIABLES DECLARATION
 ------------------------------
 DECLARE @NUMBEGIN INT
 DECLARE @NUMTABS INT
 DECLARE @COMANDO VARCHAR(8000)
 DECLARE @COMAND_BEGIN VARCHAR(10)
 DECLARE @COMAND_END VARCHAR(3)
 DECLARE @CARACTER1 INT
 --FILE SYSTEM OBJECT VARIABLES
 ------------------------------
 DECLARE @FS1 int
 DECLARE @FS2 INT
 DECLARE @RecordsExist int
 DECLARE @FileID1 int
 DECLARE @FileID2 int
 DECLARE @File1 VARCHAR(1000)
 DECLARE @File2 VARCHAR(1000)
 DECLARE @ForReading int
 DECLARE @ForWriting int
 DECLARE @ForAppending int
 DECLARE @FileLine varchar(8000)
 DECLARE @RC INT
 ------------------------------
 SET @ForReading = 1
 SET @ForWriting = 2
 SET @ForAppending = 8
 SET @RecordsExist = 0
 ------------------------------
 SET @NUMBEGIN = 0
 SET @NUMTABS = 0
 SET @RETURNVALUE = 1
 ------------------------------
 IF @NOMFIC IS NOT NULL
 BEGIN
SET NOCOUNT ON
SET @NOMFIC=RTRIM(LTRIM(@NOMFIC))
EXEC master..xp_fileexist @NOMFIC, @RC OUTPUT
IF (@@ERROR = 0) AND (@RC = 1)
BEGIN
 ------------------------------
 SET @File1 = @NOMFIC
 SET @File2 = LEFT(@NOMFIC,LEN(@NOMFIC)-4)+'-NEW.SQL'
 -- Crear un objeto de fichero para la creación del archivo de lectura
 -- Create a file object to read a file
 EXECUTE @RC = sp_OACreate 'Scripting.FileSystemObject', @FS1 OUT
 IF (@@ERROR|@RC > 0 Or @FS1 < 0) RAISERROR ('No se puede crear un objeto fichero FS1.',16,1)
 -- Crear un objeto de fichero para la creación del archivo de escritura
 -- Create a file object to write a file
 EXECUTE @RC = sp_OACreate 'Scripting.FileSystemObject', @FS2 OUT
 IF (@@ERROR|@RC > 0 Or @FS2 < 0) RAISERROR ('No se puede crear un objeto fichero FS2.',16,1)
 -- Obtener un identificador de fichero para la apertura del archivo de lectura
 -- Obtain a file identifier to open the file to read
 EXECUTE @RC = sp_OAMethod @FS1, 'OpenTextFile', @FileID1 OUT, @File1, @ForReading,True
 IF (@@ERROR|@RC > 0 Or @FileID1 < 0) RAISERROR ('No se puede abrir el fichero FileID1=%s',16,1,@File1)
 -- Obtener un identificador de fichero para la creación y/o apertura del archivo de escritura
 -- Obtain a file identifier to create/open the file to write
 EXECUTE @RC = sp_OAMethod @FS2, 'OpenTextFile', @FileID2 OUT, @File2, @ForWriting,True
 IF (@@ERROR|@RC > 0 Or @FileID2 < 0) RAISERROR ('No se puede abrir el fichero FileID2=%s',16,1,@File2)
 ------------------------------
 EXECUTE @RecordsExist = sp_OAMethod @FileID1, 'ReadLine', @FileLine OUTPUT
 WHILE @RecordsExist >= 0
 BEGIN
------------------------------
SET @COMAND_BEGIN=''
SET @COMAND_END=''
SET @COMANDO=RTRIM(LTRIM(@FileLine))
------------------------------
IF LEN(@COMANDO)>0
BEGIN
 -- Bucle para extraer el primer caracter de la cadena si es menor ó igual que el espacio en blanco chr(32) y mayor que el co¡digo 126
 -- Loop to extract the first character of the string if it is lower or equal to space chr(32) or upper than 126
 SET @CARACTER1=ASCII(LEFT(@COMANDO,1))
 WHILE (LEN(@COMANDO)>1) AND ((@CARACTER1<=32) OR (@CARACTER1>=127))
 BEGIN
IF LEN(@COMANDO)>1
BEGIN
 SET @COMANDO=RIGHT(@COMANDO,LEN(@COMANDO)-1)
 SET @CARACTER1=ASCII(LEFT(@COMANDO,1))
END
 END
 
 IF LEN(@COMANDO)>=10
 BEGIN
SET @COMAND_BEGIN=UPPER(RTRIM(LEFT(@COMANDO, 10)))
IF @COMAND_BEGIN <>'BEGIN TRAN' SET @COMAND_BEGIN=UPPER(RTRIM(LEFT(@COMANDO, 5)))
 END
 ELSE
 BEGIN
IF LEN(@COMANDO)>=5 SET @COMAND_BEGIN=UPPER(RTRIM(LEFT(@COMANDO, 5)))
 END
 IF LEN(@COMANDO)>=3 SET @COMAND_END=UPPER(RTRIM(LEFT(@COMANDO, 3)))
 IF @COMAND_BEGIN='BEGIN'
 BEGIN
SET @NUMBEGIN=@NUMBEGIN+1
SET @NUMTABS=@NUMTABS+1
IF (@NUMTABS>0) SET @COMANDO=SPACE((@NUMTABS*3))+@COMANDO
SET @NUMTABS=@NUMTABS+1
 END
 IF @COMAND_END='END'
 BEGIN
SET @NUMBEGIN=@NUMBEGIN-1
SET @NUMTABS=@NUMTABS-1
IF (@NUMTABS>0) SET @COMANDO=SPACE((@NUMTABS*3))+@COMANDO
SET @NUMTABS=@NUMTABS-1
 END
 IF (@COMAND_BEGIN<>'BEGIN') AND (@COMAND_END<>'END')
 BEGIN
IF (@NUMTABS>0) SET @COMANDO=SPACE((@NUMTABS*3))+@COMANDO
 END
END
EXECUTE @RC = sp_OAMethod @FileID2, 'WriteLine', NULL, @COMANDO
--IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido escribir en el fichero ',16,1)
IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t write to the the file ',16,1)
EXECUTE @RecordsExist = sp_OAMethod @FileID1, 'ReadLine', @FileLine OUTPUT
 END
 EXECUTE @RC = sp_OAMethod @FileID1, 'Close', NULL
 --IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido cerrar el fichero FileID1=%s',16,1,@File1)
 IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t close the file FileID1=%s',16,1,@File1)
 EXECUTE @RC = sp_OAMethod @FileID2, 'Close', NULL
 --IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido cerrar el fichero FileID2=%s',16,1,@File2)
 IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t close the file FileID2=%s',16,1,@File2)
 EXECUTE @RC = sp_OADestroy @FileID1
 --IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir la identidad del fichero FileID1',16,1)
 IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the identity of the file FileID1',16,1)
 EXECUTE @RC = sp_OADestroy @FileID2
 --IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir la identidad del fichero FileID2',16,1)
 IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the identity of the file FileID2',16,1)
 EXECUTE @RC = sp_OADestroy @FS1
 --IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir el objeto FS1',16,1)
 IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the file object FS1',16,1)
 EXECUTE @RC = sp_OADestroy @FS2
 --IF (@@ERROR|@RC > 0) RAISERROR ('No se ha podido destruir el objeto FS2',16,1)
 IF (@@ERROR|@RC > 0) RAISERROR ('Couldn´t destroy the file object FS2',16,1)
 PRINT @NUMBEGIN
 IF @NUMBEGIN<>0
 BEGIN
--PRINT 'ERROR: El fichero '+@NOMFIC+' tiene un número desigual de BEGIN y END'
PRINT 'ERROR: The File '+@NOMFIC+' have not an equal number of commands BEGIN and END'
 END
 ELSE
 BEGIN
--PRINT 'El fichero '+@NOMFIC+' tiene un número igual de BEGIN y END'
PRINT 'The File '+@NOMFIC+' have an equal number of commands BEGIN and END'
SET @RETURNVALUE=0
 END
END
ELSE
BEGIN
 --PRINT 'ERROR: El fichero '+@NOMFIC+' no existe'
 PRINT 'ERROR: The File '+@NOMFIC+' not exist'
END
 END
 ELSE
 BEGIN
PRINT 'ERROR: El argumento con el nombre del fichero esta vacio'
 END
END