EXEMPLO_1212310 Database

Parameters

Name	Type	Mode

Definition

CREATE PROCEDURE SP_COLSGLOBAIS AS
DECLARE @SQL VARCHAR(400)
DECLARE @TABELA VARCHAR(40)
DECLARE CUR_TABELA CURSOR FOR
SELECT OBJECT FROM GLIBRARY WHERE OBJECT NOT IN (SELECT OBJECT FROM GLIBRARY WHERE CHILDOBJECT = 'RECCREATEDBY' AND OBJECTTYPE = 'U') AND OBJECTTYPE = 'U' GROUP BY OBJECT
UNION
SELECT OBJECT FROM GLIBRARY WHERE OBJECT NOT IN (SELECT OBJECT FROM GLIBRARY WHERE CHILDOBJECT = 'RECCREATEDON' AND OBJECTTYPE = 'U') AND OBJECTTYPE = 'U' GROUP BY OBJECT
UNION
SELECT OBJECT FROM GLIBRARY WHERE OBJECT NOT IN (SELECT OBJECT FROM GLIBRARY WHERE CHILDOBJECT = 'RECMODIFIEDBY' AND OBJECTTYPE = 'U') AND OBJECTTYPE = 'U' GROUP BY OBJECT
UNION
SELECT OBJECT FROM GLIBRARY WHERE OBJECT NOT IN (SELECT OBJECT FROM GLIBRARY WHERE CHILDOBJECT = 'RECMODIFIEDON' AND OBJECTTYPE = 'U') AND OBJECTTYPE = 'U' GROUP BY OBJECT
DELETE FROM GLIBRARY WHERE CHILDOBJECT IN ('RECCREATEDBY','RECCREATEDON','RECMODIFIEDBY','RECMODIFIEDON')
OPEN CUR_TABELA
FETCH NEXT FROM CUR_TABELA INTO @TABELA
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER TABLE ' + @TABELA + ' ADD RECCREATEDBY VARCHAR (50)'
BEGIN TRAN
BEGIN TRY
EXEC (@SQL)
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
SET @SQL = 'ALTER TABLE ' + @TABELA + ' ADD RECCREATEDON DATETIME'
BEGIN TRAN
BEGIN TRY
EXEC (@SQL)
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
SET @SQL = 'ALTER TABLE ' + @TABELA + ' ADD RECMODIFIEDBY VARCHAR(50)'
BEGIN TRAN
BEGIN TRY
EXEC (@SQL)
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
SET @SQL = 'ALTER TABLE ' + @TABELA + ' ADD RECMODIFIEDON DATETIME'
BEGIN TRAN
BEGIN TRY
EXEC (@SQL)
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
IF NOT EXISTS (SELECT * FROM GDIC WHERE TABELA=@TABELA AND COLUNA='RECCREATEDBY')
BEGIN
EXEC ('INSERT INTO GDIC (TABELA, COLUNA, DESCRICAO, RELATORIO, APLICACOES)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'RECCREATEDBY' + '''' + ',' + '''' + 'Usuário criador do registro' + '''' + ',0,' + '''' + 'A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GDIC WHERE TABELA=@TABELA AND COLUNA='RECCREATEDON')
BEGIN
EXEC ('INSERT INTO GDIC (TABELA, COLUNA, DESCRICAO, RELATORIO, APLICACOES)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'RECCREATEDON' + '''' + ',' + '''' + 'Data de criação do registro' + '''' + ',0,' + '''' + 'A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GDIC WHERE TABELA=@TABELA AND COLUNA='RECMODIFIEDBY')
BEGIN
EXEC ('INSERT INTO GDIC (TABELA, COLUNA, DESCRICAO, RELATORIO, APLICACOES)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'RECMODIFIEDBY' + '''' + ',' + '''' + 'Autor da última modificação no registro' + '''' + ',0,' + '''' + 'A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GDIC WHERE TABELA=@TABELA AND COLUNA='RECMODIFIEDON')
BEGIN
EXEC ('INSERT INTO GDIC (TABELA, COLUNA, DESCRICAO, RELATORIO, APLICACOES)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'RECMODIFIEDON' + '''' + ',' + '''' + 'Data da última modificação no registro' + '''' + ',0,' + '''' + 'A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GLIBRARY WHERE OBJECT=@TABELA AND CHILDOBJECT='RECMODIFIEDON')
BEGIN
EXEC ('INSERT INTO GLIBRARY (OBJECT, OBJECTTYPE, CHILDOBJECT, NULLTYPE, TYPE_CHILDOBJECT)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'U' + '''' + ',' + '''' + 'RECMODIFIEDON' + '''' + ',' + '''' + 'NULL' + '''' + ',' + '''' + 'DATETIME' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GLIBRARY WHERE OBJECT=@TABELA AND CHILDOBJECT='RECCREATEDBY')
BEGIN
EXEC ('INSERT INTO GLIBRARY (OBJECT, OBJECTTYPE, CHILDOBJECT, NULLTYPE, TYPE_CHILDOBJECT)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'U' + '''' + ',' + '''' + 'RECCREATEDBY' + '''' + ',' + '''' + 'NULL' + '''' + ',' + '''' + 'varchar(50)' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GLIBRARY WHERE OBJECT=@TABELA AND CHILDOBJECT='RECCREATEDON')
BEGIN
EXEC ('INSERT INTO GLIBRARY (OBJECT, OBJECTTYPE, CHILDOBJECT, NULLTYPE, TYPE_CHILDOBJECT)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'U' + '''' + ',' + '''' + 'RECCREATEDON' + '''' + ',' + '''' + 'NULL' + '''' + ',' + '''' + 'DATETIME' + '''' + ')')
END
IF NOT EXISTS (SELECT * FROM GLIBRARY WHERE OBJECT=@TABELA AND CHILDOBJECT='RECMODIFIEDBY')
BEGIN
EXEC ('INSERT INTO GLIBRARY (OBJECT, OBJECTTYPE, CHILDOBJECT, NULLTYPE, TYPE_CHILDOBJECT)
VALUES (' + '''' + @TABELA + '''' + ',' + '''' + 'U' + '''' + ',' + '''' + 'RECMODIFIEDBY' + '''' + ',' + '''' + 'NULL' + '''' + ',' + '''' + 'varchar(50)' + '''' + ')')
END
FETCH NEXT FROM CUR_TABELA INTO @TABELA
END
CLOSE CUR_TABELA
DEALLOCATE CUR_TABELA