miércoles, agosto 23, 2006

Dependencias de un Stored Procedure

Como bien es sabido nunca aprende uno tanto como cuando necesita hacer algo y no sabe como... anduve buscando en internet alguna forma "elegante" de obtener las dependencias que pudiera contener un Stored Procedure sobre otras tablas y al parecer no hay nada que funcione de forma directa, si bien es cierto que puedes utilizar las tablas y vistas "sysdepends" tambien es cierto que estas no son 100% confiables ya que las referencias que aqui pudieramos encontrar dependen del orden en que se hayan creado los objetos de la base de datos.
Despues de darle muchas vueltas decidi que si no hay forma infalible y 100% exacta de obtener esta informacion entonces trataria de acercarme lo mas posible, dado que tenia alrededor de unos 50 Stored procedures y 335 tablas, se imaginaran la "talachita" que iba a ser obtener esos datos abriendo cada uno de los SP's y analizando el codigo.Y esta fue la solucion con la que al final me quede...

/**1. Primero hay que declarar una variable del tipo tabla con 3 columnas que me sirvan para conocer mas de los objetos encontrados **/


DECLARE @info TABLE(owner varchar(15), tableName varchar(100), oName varchar(100))

/**2. Declarar unas variables mas para almacenar el nombre de mi tabla y el identificador que SQL le asigna a la tabla**/


DECLARE @objname varchar(100), @objid int

/**3. Obtener el identificador de la tabla**/

SET @objname = 'TablaClientes'
SET @objid = object_id(@objname)

/**4. Insertar en la variable tabla los datos que correspondan a los Stored Procedures que en su codigo incluyan el nombre de la tabla que ando buscando**/

INSERT INTO @info(tableName, owner, oName)
SELECT @objname, routine_schema , routine_nameFROM information_schema.routines where routine_definition like ('%' + @objName + '%')GROUP BY routine_schema, routine_name

/**5 . Realizar la consulta sobre la variable de tabla**/

SELECT * FROM @info




Con el script anterior, puedo consultar los Stored Procedures que mencionan una tabla, pero aun asi, tengo 335 tablas!!! SIN contar los que pudieran estar referenciando a alguna vista... y ahora quien podra defenderme? ... CODESMITH!!!
Total que he creado tambien una plantilla donde puedo barrer las 335 tablas + 50 Vistas (aprox) y generar el script que al final me dara la informacion que ando buscando... asi que si bien este metodo no es 100% exacto, si me saca de este apuro.

Ventajas
  • Estoy directamente buscando las referencias por medio del codigo del stored procedure
  • En casos como el que platico, donde tengo mas de 10 tablas hacer esto "a pie" es una lata
  • Es extremadamente rapido

Y las desventajas...

  • El barrido que hace del codigo del Stored Procedure, no distingue entre comentarios y codigo "vivo"
  • Las tablas y vistas deben pertenecer a la misma base de datos de los stored procedures
  • Estoy usando LIKE para hacer las busquedas asi que si una tabla se llama "tblCliente" y otra "tblClienteResultado"... se imaginaran que si un SP esta utilizando "tblClienteResultado" tambien me aparecera como referencia "fantasma" la tabla "tblCliente"

Pero hey... que querias en tan poco tiempo.

Aqui esta la plantilla de CodeSmith que use.

4 comentarios:

  1. Tipos de integridad de datos
    Integridad de dominio (columnas) DEFAULT CHECK
    Integridad de Entidad (filas) PRIMARY KEY UNIQUE
    Integridad Referencial (entre tablas) FOREIGN KEY
    Crear un constraints Default: Integridad de Dominio
    alter table empresa add constraint def_telef default '000-0000' for telefono
    Crear un constraints Check: Integridad de Dominio
    alter table empresa add constraint ddd check (paginas>0 and preciocompra>0)
    Crear un constraints Primary Key: Integridad de Entidad
    alter table empresa add /*constraint*/ pk_idclave primary key (idclave)
    Crear un constraints Unique: Integridad de Entidad
    ALTER TABLE empresa ADD CONSTRAINT nnn_clave_empresa UNIQUE (descrip)
    Crear un constraints de Integridad referencial
    Alter Table supervisorempresa Add Constraint FK_codtipoemp Foreign Key (codempresa) References empresa(idclave)
    Forzar integridad de datos
    Integridad declarativa
    1. Criterios definidos en un objeto
    2. Implementar usando constraints
    Integridad por procedimientos
    1. Forzar scripts
    2. Implementar triggers y stored Procedures
    Que es una Esquema
    Es un conjunto de datos para estructurar datos del mundo real.
    Es la percepción de una determinada realidad interpretada de acuerdo a un cierto modelo.
    Que es Generalización
    Permite formar una entidad mediante la unión de otras entidades.
    Cuales son los tipos de Cardinalidad
    Es una restricción que se aplica a los tipos de relaciones sobre las entidades.
    (Uno a uno) (Uno a muchos) (Muchos a muchos)
    Diseño Inicial de la Base de Datos
    1. Mapear las entidades para las tablas
    2. Mapear atributos para columnas y documentar datos simples
    3. Mapear identificadores únicos a llaves primarias
    4. Mapear relaciones a llaves foráneas
    5. Elegir opciones de arco
    6. Elegir opciones de subtipo
    ¿Qué es un Stored Procedure?
    1. Colección organizada de sentencias Transact-SQL
    2. Encapsula tareas repetitivas
    3. 5 tipos : Sistema, Local, Temporal, Remotos, Extendidos)
    4. Aceptan parámetros de entrada y retornan valores
    5. Devuelven valores que informan si hubo éxito o fracaso en las operaciones



    Ventajas de los Stored Procedures
    1. Compartir lógica de negocios
    2. Provee mecanismos de seguridad
    3. Mejoran la performance
    4. Reducen el tráfico en la red

    ResponderBorrar
  2. Ventajas de los Stored Procedures

    Tipos de integridad de datos
    Integridad de dominio (columnas) DEFAULT CHECK
    Integridad de Entidad (filas) PRIMARY KEY UNIQUE
    Integridad Referencial (entre tablas) FOREIGN KEY
    Crear un constraints Default: Integridad de Dominio
    alter table empresa add constraint def_telef default '000-0000' for telefono
    Crear un constraints Check: Integridad de Dominio
    alter table empresa add constraint ddd check (paginas>0 and preciocompra>0)
    Crear un constraints Primary Key: Integridad de Entidad
    alter table empresa add /*constraint*/ pk_idclave primary key (idclave)
    Crear un constraints Unique: Integridad de Entidad
    ALTER TABLE empresa ADD CONSTRAINT nnn_clave_empresa UNIQUE (descrip)
    Crear un constraints de Integridad referencial
    Alter Table supervisorempresa Add Constraint FK_codtipoemp Foreign Key (codempresa) References empresa(idclave)
    Forzar integridad de datos
    Integridad declarativa
    1. Criterios definidos en un objeto
    2. Implementar usando constraints
    Integridad por procedimientos
    1. Forzar scripts
    2. Implementar triggers y stored Procedures
    Que es una Esquema
    Es un conjunto de datos para estructurar datos del mundo real.
    Es la percepción de una determinada realidad interpretada de acuerdo a un cierto modelo.
    Que es Generalización
    Permite formar una entidad mediante la unión de otras entidades.
    Cuales son los tipos de Cardinalidad
    Es una restricción que se aplica a los tipos de relaciones sobre las entidades.
    (Uno a uno) (Uno a muchos) (Muchos a muchos)
    Diseño Inicial de la Base de Datos
    1. Mapear las entidades para las tablas
    2. Mapear atributos para columnas y documentar datos simples
    3. Mapear identificadores únicos a llaves primarias
    4. Mapear relaciones a llaves foráneas
    5. Elegir opciones de arco
    6. Elegir opciones de subtipo
    ¿Qué es un Stored Procedure?
    1. Colección organizada de sentencias Transact-SQL
    2. Encapsula tareas repetitivas
    3. 5 tipos : Sistema, Local, Temporal, Remotos, Extendidos)
    4. Aceptan parámetros de entrada y retornan valores
    5. Devuelven valores que informan si hubo éxito o fracaso en las operaciones



    Ventajas de los Stored Procedures
    1. Compartir lógica de negocios
    2. Provee mecanismos de seguridad
    3. Mejoran la performance
    4. Reducen el tráfico en la red

    ResponderBorrar
  3. Humberto, no existe mas la liga de la plantilla codesmith para buscar los storeprocedure. La volveras a poner?

    ResponderBorrar