Busqueda de objetos en una base de datos

Este procedimiento almacenado resulta útil para encontrar diferentes objetos en las bases de datos. No siempre sabemos de memoria los nombres de los objetos. Si queremos obtener datos de algunas tablas o vistas o ejecutar un procedimiento o función, y no nos acordamos exactamente del nombre de los objetos, con este procedimiento lo podemos averiguar de una manera rápida y fácil. Este procedimiento ha sido probado en las versiones 2005 y 2008. Creándolo sobre la base de datos model de SQL Server, logramos que el procedimiento almacenado exista en cualquier nueva base de datos que se cree. Este procedimiento almacenado construye dinámicamente una consulta dependiendo de los parámetros que se usen. 

Se pueden buscar todos los objetos u objetos de algún tipo en especial, con la opción de ordenarlos por su fecha de creación. Muestra al final del resultado un contador de los objetos que corresponden a los criterios de búsqueda. 

Sintaxis:

p_busca nombre, [tipo_objeto], [flag_orden]

Donde:

nombre: Este parámetro es obligatorio. Es el nombre o parte del nombre del objeto que se desea buscar. Puede ser cualquier combinación de letras o números. Se pueden usar comodines de busqueda (%)

tipo_objeto: Este parámetro es opcional. Limita el resultado al tipo de objeto que se indique. Los tipos de objeto que se pueden buscar son:

  • C = CHECK constraint (Restricciones de chequeo)
  • D = Default or DEFAULT constraint (Restricciones por defecto)
  • F = FOREIGN KEY constraint (Restricciones de clave foranea)
  • L = Log (Bitácora)
  • FN = Scalar function (Función escalar)
  • IF = Inlined table-function (Función de valores de tabla en línea)
  • P = Stored procedure (Procedimiento almacenado)
  • PK = PRIMARY KEY constraint (type is K) (Restricciones de clave primaria)
  • RF = Replication filter stored procedure (Procedimiento almacenado de filtro de recuperación)
  • S = System table (tabla de sistema)
  • TF = Table function (función de tabla)
  • TR = Trigger (disparador)
  • U = User table (tabla de usuario)
  • UQ = UNIQUE constraint (type is K) (Restricción única)
  • V = View (Vista)
  • X = Extended stored procedure (Procedimiento Almacenado Extendido)

flag_orden: Este parámetro es opcional. Sirve para indicar si deseamos ordenar el resultado por la fecha de creación del objeto en orden descendente.

 Ejemplos de Uso:

  • Buscar todos los tipos de objeto, cuyo nombre empieza con cli (no es necesario usar las comillas ”). El resultado será ordenado por el nombre del objeto:
    •  p_busca cli
  • Buscar todos los tipos de objetos, cuyo nombre tenga en alguna parte los caracteres cli. El resultado será ordenado por el nombre del objeto:
    • p_busca ‘%cli’
  • Buscar todos los tipos de objetos, cuyo nombre tenga en alguna parte los caracteres cli. El resultado será ordenado por fecha de creación:
    • p_busca ‘%cli’, null, 1
  • Para buscar una tabla , cuyo nombre empieza por cli (no es necesario usar las comillas”). El resultado será ordenado por nombre:
    •  p_busca cli, u
  • Para buscar un procedimiento almacenado, cuyo nombre empieza por cli (no es necesario usar las comillas”). El resultado será: ordenado por fecha de creación:
    • p_busca cli, p, 1

A continuación el script del procedimiento:

 

 

 

/*

———————————————————————–

PROPOSITO            | Busca objetos en una base de datos, por tipo

                     | o por nombre, Opción de ordenarlos por

                     | su fecha de creación. Muestra al final un contador

                     | con el total de objetos ubicados.

———————————————————————–

 PARAMETROS ENTRADA   | @ObjectName-nombre o parte del nombre del objeto

                     | @Type-tipo de objeto según lista

                     | @SortByDate-flag orden por fecha de creación

———————————————————————–

 PARAMETROS DE SALIDA | NA

———————————————————————–

CREADO POR           | Alberto De Rossi

FECHA CREACION       | 17-03-2008

———————————————————————–

HISTORIAL DE CAMBIOS | FECHA      RESPONSABLE         MOTIVO

                     | ———- ——————- —————–

                     |

———————————————————————–

PRUEBA              

p_busca cli->cualquier objeto cuyo nombre que comience con cli

p_busca ‘%log’->cualquier objeto que comience contenga cli en el nombre

p_busca ‘%cli’,null,1->objetos que contenga cli en nombre ordenado por fecha creación

p_busca ”,u->lista todas las tablas

*/

ALTER PROCEDURE [dbo].[p_busca]

@ObjectName varchar(50),

@Type varchar(2) = NULL,

@SortByDate bit = NULL

AS

 

–Variable para consulta dinámica

DECLARE @Query varchar(500)

 

–Añadimos ‘%’ al final del nombre que nosotros queremos buscar

SELECT @ObjectName = @ObjectName + ‘%’

 

–Construimos la consulta dinamicamente basados en los parametros

SET @Query = ‘SELECT SCHEMA_NAME(schema_id) as [Esquema],’

 SET @Query = @Query + ‘ name AS [Objecto], type AS [Tipo],’

 SET @Query = @Query + ‘ create_date [FechaCreacion]’

 SET @Query = @Query + ‘ FROM sys.objects’

 SET @Query = @Query + WHERE name LIKE ”’ + @ObjectName + ””

 

–Si especificamos el tipo, lo añadimos a la condición where

IF @Type IS NOT NULL

  SET @Query = @Query + ‘ AND type = ”’ + UPPER(@Type) + ””

 

–Si especificamos la opción SortByDate = 1:

–se añade a consulta y ordena por fecha de creación

IF @SortByDate = 1

  SET @Query = @Query + ‘ ORDER BY create_date DESC’

ELSE

  SET @Query = @Query + ‘ ORDER BY name’

 

–Clausula COMPUTE para contar las lineas mostradas

SET @Query = @Query + ‘ COMPUTE COUNT(name)’

 

EXEC (@Query)

GO

 

 

Deja una respuesta

Tu dirección de correo electrónico no será publicada.