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.

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