Cómo buscar objetos de base de datos y tablas en SQL Server

Hola y bienvenido a la cuarta parte de nuestra serie de artículos para ayudarlo a aprender cómo desarrollar bases de datos y trabajar con ellas. Esta vez vamos a profundizar en la compleja tarea de encontrar datos y objetos en SQL Server, que se llevará a cabo primero usando scripts estándar y luego usando la herramienta de búsqueda dbForge.

Muy a menudo nos enfrentamos a una situación en la que necesitamos encontrar:

  1. Objeto de base de datos (tabla, vista, procedimiento almacenado, función, etc.).
  2. Datos (valor y tabla que lo contiene).
  3. Un fragmento de código en una definición de objeto de base de datos.
Índice

    Busque datos y objetos en SQL Server usando scripts estándar

    Primero, mostremos cómo hacer esto con scripts estándar.
    Puede buscar la tabla de empleados en la base de datos utilizando el siguiente script:

    select [object_id], [schema_id],
    	   schema_name([schema_id]) as [schema_name], 
    	   [name], 
    	   [type], 
    	   [type_desc], 
    	   [create_date], 
    	   [modify_date]
    from sys.all_objects
    where [name]='Employee'

    El resultado del script será el siguiente:

    Figura 1. Resultados de la búsqueda en la tabla de empleados

    Muestra:

    1. Identificadores para el objeto y el esquema donde se encuentra el objeto.
    2. Nombre de esquema y nombre de objeto.
    3. Tipo de objeto y descripción.
    4. Fecha y hora en que se creó y modificó el objeto.

    Para encontrar todas las entradas en la línea "Proyecto", puede usar el siguiente script:

    select [object_id], [schema_id],
    	   schema_name([schema_id]) as [schema_name], 
    	   [name], 
    	   [type], 
    	   [type_desc], 
    	   [create_date], 
    	   [modify_date]
    from sys.all_objects
    where [name] like '%Project%'

    El resultado será la siguiente tabla:

    Figura 2. El resultado de la búsqueda de la subcadena "Proyecto" en todos los objetos de la base de datos.

    Como se muestra como resultado, la fila "Proyecto" está contenida no solo en las tablas Project y ProjectSkill, sino también en algunas claves primarias y externas.

    Para comprender qué tabla contiene estas claves, agreguemos el campo parent_object_id, su nombre y el esquema en el que se encuentra, a la salida haciendo lo siguiente:

    select ao.[object_id], ao.[schema_id],
    	   schema_name(ao.[schema_id]) as [schema_name],
    	   ao.parent_object_id,
    	   p.[schema_id] as [parent_schema_id],
    	   schema_name(p.[schema_id]) as [parent_schema_name],
    	   p.[name] as [parent_name],
    	   ao.[name], 
    	   ao.[type], 
    	   ao.[type_desc], 
    	   ao.[create_date], 
    	   ao.[modify_date]
    from sys.all_objects as ao
    left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
    where ao.[name] like '%Project%'

    El resultado es una tabla con información detallada sobre los objetos padre, es decir, dónde se establecen las claves principal y externa:

    Figura 3. El resultado de la búsqueda de la subcadena "Proyecto" en todos los objetos de la base de datos con los detalles del objeto principal.

    Los siguientes objetos del sistema se utilizan en las consultas:

    1. Tabla sys.all_objects.
    2. Función Schema_name con valor escalar.

    Puede encontrar valores de fila en todas las tablas de la base de datos con esta solución. Simplifiquemos esta solución y mostremos cómo encontrar, por ejemplo, el valor de "Ramiro" usando el siguiente script:

    set nocount on
    declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
    set @substr="%Ramiro%"
    
    declare @sql nvarchar(max);
    
    create table #rslt 
    (table_name varchar(128), field_name varchar(128), [value] nvarchar(max))
    
    declare s cursor for select table_name as table_name from information_schema.tables where table_type="BASE TABLE" order by table_name
    open s
    fetch next from s into @name
    while @@fetch_status = 0
    begin
     declare c cursor for 
    	select quotename(column_name) as column_name from information_schema.columns 
    	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
     set @name = quotename(@name)
     open c
     fetch next from c into @column
     while @@fetch_status = 0
     begin
       --print 'Processing table - ' + @name + ', column - ' + @column
    
       set @sql="insert into #rslt select "'' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
    	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';
    
    	print @sql;
    
       exec(@sql);
    
       fetch next from c into @column;
     end
     close c
     deallocate c
     fetch next from s into @name
    end
    select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
    group by table_name, field_name
    order by table_name, field_name
    
    drop table #rslt
    close s
    deallocate s

    La salida puede ser la siguiente:

    Figura 4. resultado de la búsqueda en la base de datos

    Aquí la salida muestra los nombres de las tablas y en qué columnas se almacena el valor que contiene la subcadena "Ramiro", así como el número de salidas encontradas de este sustrato para las coincidencias tabla-columna encontradas.

    Puede utilizar las siguientes vistas del sistema para buscar objetos cuyas definiciones contengan estos fragmentos de código:

    1. sys.sql_modules
    2. sys.all_sql_modules
    3. sys.syscomentarios

    Por ejemplo, con la última vista, puede buscar todos los objetos cuyas definiciones contengan este fragmento de código con el siguiente script:

    select obj.[object_id],
    	   obj.[name],
    	   obj.[type_desc],
    	   sc.[text]
    from sys.syscomments as sc
    inner join sys.objects obj on sc.[id]=obj.[object_id]
    where sc.[text] like '%code snippet%'

    Aquí el resultado muestra el identificador, nombre, descripción y definición completa del objeto.

    Busque datos y objetos de base de datos usando dbForge Search

    Es mucho más conveniente buscar datos y objetos por medio de medios improvisados. Una de esas herramientas es dbForge Search.

    Para llamar a esta herramienta, haga clic en dbForge Search en la ventana de SSMS. Aparecerá un cuadro de búsqueda:

    Figura 5 Ventana de búsqueda de dbForge

    Preste atención al panel superior (de izquierda a derecha) ya que puede cambiar:

    • Modo de búsqueda (búsqueda de DDL (objetos) o datos).
    • Lo que realmente estamos buscando (qué subcadena).
    • Regístrese, busque una coincidencia exacta para una palabra o busque entradas en cadenas.

    Figura.6. Modos de búsqueda

    Tú también puedes:

    • Agrupa los resultados por tipo de objeto usando el botón central superior (dos cuadrados conectados por una llave).
    • Seleccione los tipos de objetos requeridos para buscar:

    Figura.7. Seleccionar tipos de objetos para buscar

    • Configure múltiples bases de datos para buscar y seleccionar una instancia de MS SQL Server

    Este es el modo de búsqueda de objetos cuando DDL está habilitado:

    Figura 8. Buscar objetos DDL

    La única diferencia en el modo de búsqueda de datos es la elección del tipo de objeto:

    Figura.9. Buscar por datos

    Es decir, solo las tablas en las que se almacenan datos están disponibles para la selección:

    Figura.10. Seleccionar tablas para buscar datos

    Ahora, de la misma manera que antes, busquemos todos los registros de la subcadena "Proyecto" en los nombres de los objetos:

    Figura.11. El resultado de la búsqueda de todos los registros de la línea "Proyecto" en los nombres de los objetos de la base de datos.

    Aparentemente, el modo de búsqueda está configurado por objetos DDL, estamos buscando la línea "Proyecto", por lo que se llena, todo lo demás se deja por defecto.

    Cuando selecciona el objeto resultante, puede ver el código de identificación de ese objeto y su objeto principal a continuación.

    Alternativamente, puede mover la navegación al objeto resultante haciendo clic en el botón a continuación:

    Figura.12. Transferir la navegación al objeto recibido

    También puede agrupar los objetos encontrados por su tipo:

    Figura.13. Resultado de la búsqueda de objetos agrupados por tipo

    Tenga en cuenta que incluso se muestran tablas que contienen campos cuyos nombres incluyen la subcadena "Proyecto". Sin embargo, déjame recordarte que el modo de búsqueda se puede cambiar: busca una coincidencia total o parcial, caso o no.

    Ahora busquemos el valor de "Ramiro" en todas las tablas:

    Figura.14. El resultado de la subcadena de búsqueda "Ramiro" en todos los datos de la base de datos

    Tenga en cuenta que se muestran todas las filas que contienen la subcadena "Ramiro" en la tabla Empleado seleccionada.

    Alternativamente, puede mover la navegación al objeto encontrado haciendo clic en el botón de abajo, como antes:

    Figura.15. Transferir la navegación al objeto encontrado

    Como resultado, podemos buscar los objetos y datos correctos en la base de datos.

    Conclusión

    En pocas palabras, hemos recorrido un largo camino desde la idea hasta la implementación de la base de datos para el servicio de contratación. Resumamos lo que hemos logrado hasta ahora:

    Eso es todo, la base de datos está lista para usar, y después de ejecutar las pruebas se puede integrar y existirá.

    ¿Qué es lo siguiente? Posteriormente necesitaremos mantener la base de datos y hacer los cambios necesarios.

    El único otro aspecto que aún no se cubre es la aparición de objetos no válidos y las formas de encontrar objetos no válidos en una base de datos de SQL Server.

    Artículos de interés

    Subir