Hola a todos, les comento:
Yo le envio a mi SP El nombre de una Base de Datos (Por ejemplo AdventureWorks) y el nombre de un schema (Por Ejemplo HumanResources) y en base a eso tengo que crear una nueva tabla "Resultado" en donde figuren todos los SP y Tablas que componen ese Schema.
Entre los datos que me piden esta que si es una TABLA indique si posee PK y FK, por lo cual a traves del INFORMATION_SCHEMA obtengo por una parte las tablas y por el otro las constraint, por lo cual tendria que hacer un join.
Los problemas en este caso son 2:
- Yo estoy parado sobre otra base de datos y estas consultas las hago contra AdventureWorks
- Tengo que recorrer SI O SI las filas por que cada fila puede tener diferentes resultados
Asi que lo que se me ocurrio, aunque poco practico, es usar dentro de mi SP un cursor, pero me da el error:
Could not complete cursor operation because the set options have changed since the cursor was declared.
Les dejo el codigo completo a ver si me pueden ayudar por qeu me estoy volviendo loco!!
Código SQL:
Ver original-- ======================================================= --
-- ===========> CREACION DE STORAGE PROSEDURE <=========== --
-- ======================================================= --
CREATE PROCEDURE sp_grabar @SCHEMA VARCHAR(50), @DB VARCHAR(50)
AS
BEGIN
DECLARE @ERRORMSJ VARCHAR(MAX) -- Variable de Mensaje de Error
DECLARE @PK VARCHAR(2) -- Variable de SI o NO
DECLARE @PK_Compuesta VARCHAR(2) -- Variable de SI o NO
DECLARE @FK VARCHAR(2) -- Variable de SI o NO
DECLARE @FK_Compuesta VARCHAR(2) -- Variable de SI o NO
DECLARE @CANTIDAD INT -- Variable Acumuladora
DECLARE @query NVARCHAR(MAX) -- Variable para consultas dinamicas
DECLARE @INICIO INT -- Variable para recorrer el while
DECLARE @cambiarDB VARCHAR(MAX) -- Declaro una variable para poder cambiar de DB en el codigo
SET @cambiarDB='USE '+@DB+ '; '
BEGIN TRY
---------------------------------------------------------------------------
-- ================> Verifico si la Base de Datos Existe <============== --
---------------------------------------------------------------------------
/*
SET @ERRORMSJ = 'ERROR: La Base de Datos no existe'
IF DB_ID(@DB) IS NULL RAISERROR(@ERRORMSJ,16,1)
*/
---------------------------------------------------------------------------
-- ======================> Verifico PK y PK COMPUESTA <==================== --
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- ===========> Hago los Insert para las Tablas del Esquema <=========== --
---------------------------------------------------------------------------
-- Declaro el cursor
SET @query=@cambiarDB+'DECLARE cursorTablas CURSOR LOCAL FOR(SELECT * FROM INFORMATION_SCHEMA.TABLES)'
EXECUTE sp_executesql @query
-- Declaro variables para manejar el cursor
DECLARE @TABLE_CATALOG NVARCHAR(MAX)
DECLARE @TABLE_SCHEMA NVARCHAR(MAX)
DECLARE @TABLE_NAME NVARCHAR(MAX)
DECLARE @TABLE_TYPE NVARCHAR(MAX)
-- Hago la apertura del Cursor
OPEN cursorTablas
-- Recorro el Cursor
FETCH NEXT FROM cursorTablas INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Resultado(esquema, base, objeto, tipoObjeto) VALUES (@TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE)
FETCH NEXT FROM cursorTablas INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE
END
close cursorTablas
Deallocate cursorTablas
/*
SET @query='INSERT INTO Resultado(esquema, base, objeto, tipoObjeto) SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from ' + @DB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE=' +'''BASE TABLE''' + ' AND TABLE_SCHEMA=' + '''' + @SCHEMA + ''''
EXEC sp_executesql @query
*/
---------------------------------------------------------------------------
-- =============> Hago los Insert para los SP del Esquema <============= --
---------------------------------------------------------------------------
SET @query='INSERT INTO Resultado(esquema, base, objeto, tipoObjeto, parametros) SELECT DISTINCT rou.SPECIFIC_CATALOG as Base, rou.SPECIFIC_SCHEMA as Esquema, rou.ROUTINE_NAME as Nombre, rou.ROUTINE_TYPE as Tipo,
CASE WHEN par.PARAMETER_MODE IN (''IN'',''INOUT'') THEN ''SI'' ELSE ''NO'' END as Parametros
FROM ' + @DB + '.INFORMATION_SCHEMA.ROUTINES as rou
INNER JOIN ' + @DB + '.INFORMATION_SCHEMA.PARAMETERS as par
ON rou.ROUTINE_NAME = par.SPECIFIC_NAME
WHERE ROUTINE_TYPE = ''PROCEDURE'' AND rou.SPECIFIC_SCHEMA = ' + '''' + @SCHEMA + ''''
EXEC sp_executesql @query
END TRY
BEGIN CATCH -- Inserto el error en la tabla de Logs de Errores
INSERT INTO LOGERRORES (descripcion, error, fila, errorLinea, errorMensaje, errorProcedure, errorNumero, errorSeveridad, errorEstado, fececha, idUsuario) VALUES
(@ERRORMSJ, @@ERROR, @@ROWCOUNT, ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_SEVERITY(),ERROR_STATE(), GETDATE(),SYSTEM_USER)
RAISERROR (@ERRORMSJ, 16 , 1)
END CATCH
END
GO
-- ======================================================= --
-- ===================> CONSULTAS <======================= --
-- ======================================================= --
/* */
EXEC sp_grabar HumanResources, AdventureWorks
---------------------------------------------------------------------------
-- =======================> Listado de Select <========================= --
---------------------------------------------------------------------------
SELECT * FROM Auditoria
SELECT * FROM LOGERRORES
SELECT esquema AS ESQUEMA,
base AS BASE,
objeto AS OBJETO,
ISNULL(tipoObjeto,' ') AS 'TIPO DE OBJETO',
ISNULL(parametros,' ') AS PARAMETROS,
ISNULL(pk,' ') AS PK,
ISNULL(pkCompuesta,' ') AS 'PK COMPUESTA',
ISNULL(fk,' ') AS FK,
ISNULL(fkCompuesta,' ') AS 'FK COMPUESTA'
FROM Resultado