Critza | Base de Datos

Script para obtener la definición de una tabla en Sql Server.

16 octubre 2019 Administrador Base de Datos, SQL Server

SELECT
	OBJECT_NAME(C.OBJECT_ID) AS 'Tabla'
	,C.name AS 'Columna'
	,SCHEMA_NAME(T.schema_id) AS 'Esquema'
	,T.name AS 'Tipo'	
	,C.max_length AS 'Tamaño Maximo en Bytes'
	,C.precision AS 'Precisión'
	,C.scale AS 'Escala'
FROM 
	sys.columns AS C
JOIN 
	sys.types AS T 
ON 
	C.user_type_id = T.user_type_id
WHERE 
	OBJECT_NAME(C.OBJECT_ID)='Card' 
ORDER BY 
	C.OBJECT_ID;

Concatenar valores de una columna en SQL Server.

06 noviembre 2018 Administrador Base de Datos, SQL Server

begin
	--Tabla temporal que se crea y llena para simular una tabla de la base de datos.
	declare @TablaOrigen table(Id int, Color varchar(10));

	insert into @TablaOrigen(Id,Color) values(1,'Azul');
	insert into @TablaOrigen(Id,Color) values(2,'Purpura');
	insert into @TablaOrigen(Id,Color) values(3,'Negro');
	insert into @TablaOrigen(Id,Color) values(4,'Cafe');
	insert into @TablaOrigen(Id,Color) values(5,'Violeta');
	insert into @TablaOrigen(Id,Color) values(6,'Naranja');
	insert into @TablaOrigen(Id,Color) values(7,'Rojo');
	insert into @TablaOrigen(Id,Color) values(8,'Verde');
	insert into @TablaOrigen(Id,Color) values(9,'Cian');
	insert into @TablaOrigen(Id,Color) values(10,'Amarillo');

	--Variables utilizadas para la concatenación.
	declare @TablaTemporal table(Elemento varchar(10));
	declare @TotalElementos int=0;
	declare @Elemento varchar(10)='';
	declare @Resultado varchar(max)='';
	declare @EsPrimerElemento bit=1;

	--Se inserta en la tabla temporal '@TablaTemporal' todos los elementos de la tabla origen '@TablaOrigen'.
	insert into @TablaTemporal(Elemento) select Color from @TablaOrigen;

	--Se asigna a la variable '@TotalElementos' el número total de registros almacenados en la tabla temporal '@TablaTemoral'.
	set @TotalElementos= (select count(Elemento) from @TablaTemporal);

	--Se recorre la tabla temporal '@TotalElementos' con el fin de ir concatenando cada uno de sus valores en la variable '@Resultado'.
	while @TotalElementos > 0
		begin				
			set @Elemento= (select top 1 Elemento from @TablaTemporal);

			if(@EsPrimerElemento=1)
				begin
					set @Resultado= @Elemento;
					set @EsPrimerElemento=0;		
				end
			else
				begin
					set @Resultado= concat(@Resultado,',',@Elemento);
				end

			delete top(1) from @TablaTemporal;

			set @TotalElementos= @TotalElementos - 1;
		end

		--Se lleva a cabo la selección de la variable '@Resultado' para visualizar el resultado del proceso de la concatenación.
		select @Resultado as 'Resultado';
end

Resultado:

Consultas paginadas en MySql.

11 febrero 2017 Administrador Base de Datos, MySql

El consumo de información accediendo a bases de datos es clave para cualquier tipo de aplicación, por lo que es necesario que el consumo de la información se lleve a cabo en el menor tiempo posible y utilizando el menor número de recursos del servidor; esto con el fin de mejorar el tiempo de respuesta de las aplicaciones.

Es un hecho que muchas veces los desarrolles se enfrentan al reto de mostrar una consulta de información en un grid, el cual en todos los casos debe de tener elementos gráficos de paginación para que el usuario pueda navegar entre el conjunto de datos. En el escenario que se plantea, generalmente los desarrolladores hacen uso de malas prácticas, y a que voy con esto, a que por facilidad obtienen todo el conjunto de información de la base de datos y en capas superiores de la arquitectura de su aplicación empiezan a paginarla para cargarla en el grid para cada una de las paginas, lo que realmente es un consumo incorrecto de la información, ya que la manera más correcta de llevar a cabo el proceso es solicitar solamente aquellos datos que necesitas mostrar al usuario para la página que desea visualizar del grid, logrando así un óptimo tiempo de respuesta en la aplicación tanto en la visualización y paginación de la información en el grid.

A continuación te presento un ejemplo de cómo crear consultas paginadas desde base de datos, el cual puedes tomar como referencia para crear las consultas de datos de tus proyectos.

Creación de Base de Datos de Prueba:

create database BD_Pruebas;

use BD_Pruebas;

create table Persona
(
personaId int not null auto_increment primary key,
nombre varchar(20) not null,
apellidoPaterno varchar(20) not null,
apellidoMaterno varchar(20) not null,
genero varchar(9) not null,
fechaNacimiento datetime not null,
fechaCreacion datetime not null,
activo tinyint(1) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Creación de Insumos:

INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Armando','Rosales','Maza','Masculino','1988-10-12',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Luis','Perez','Hernandez','Masculino','1980-10-01',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Andrea','Sanchez','Gonzalez','Femenino','1988-01-20',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Monica','Lopez','Jimenez','Femenino','1970-09-15',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Hugo','Morales','Quiroz','Masculino','1985-04-18',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('David','Gonzalez','Rodriguez','Masculino','2000-01-29',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Karime','Lozano','Herrera','Femenino','1995-11-15',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Mario','Fernandez','Gutierrez','Masculino','2001-04-17',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Liliana','Duran','Roano','Femenino','1984-09-25',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Francisco','Zuñiga','Zapata','Masculino','1990-01-06',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Diego','Martinez','Roman','Masculino','1986-02-14',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jimena','Ortiz','Morales','Femenino','1993-12-24',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Tania','Ordaz','Montiel','Femenino','1987-07-05',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Ana','Arriaga','Linares','Femenino','1996-10-16',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Alejandra','Tamez','Vazquez','Femenino','2002-05-28',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Francisco','Mota','Marin','Masculino','1978-06-29',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Martin','Lutero','Lara','Masculino','1980-09-25',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Kendra','Muñiz','Mora','Femenino','1976-01-20',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Cristian','Beltran','Solis','Masculino','1987-11-15',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Natalia','Montes','Herrera','Femenino','1987-03-16',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Kenia','Martinez','Rodriguez','Femenino','1994-04-27',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Ricardo','Maldonado','Soto','Masculino','1998-11-02',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Susana','Perez','Lopez','Femenino','1992-05-20',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Ingrid','Solano','Jimenez','Femenino','1998-09-28',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Gerardo','Montiel','Oronzor','Masculino','1970-01-09',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Gabriel','Robles','Martinez','Masculino','1978-05-24',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Fany','Roman','Nuñez','Femenino','1990-08-09',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jamil','Morales','Santos','Masculino','1996-12-23',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Tamara','Sanchez','Ramirez','Femenino','1970-01-02',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Bernardo','Lopez','Gonzalez','Masculino','1995-10-10',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jaime','Jimenez','Sandria','Masculino','1980-12-10',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Mabel','Fernandez','Martinez','Femenino','1997-05-12',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Jacobo','Montes','Ortiz','Masculino','1978-06-22',now(),1);
INSERT INTO `bd_pruebas`.`persona`(`nombre`,`apellidoPaterno`,`apellidoMaterno`,`genero`,`fechaNacimiento`,`fechaCreacion`,`activo`) 
VALUES ('Maria','Duran','Zamora','Femenino','1999-09-07',now(),1);

Procedimiento almacenado para encapsular la función de paginación y su respectiva invocación.

delimiter //
create procedure ObtenDatosPaginados(in numeroPagina int, in tamanioPagina int)
begin
	if (numeroPagina < 1 or tamanioPagina < 1) then
		select *, 0 as 'TotalRegistros', 0 as 'TotalPaginas' 
        from Persona
		limit 0,0;
	else
		begin
			declare _totalRegistros int default (select count(*) from Persona);                        
            declare _totalPaginas int default 0;
            declare _offset int default 0;            
                        		
            if ((_totalRegistros % tamanioPagina) = 0) then
				set _totalPaginas = _totalRegistros / tamanioPagina;                
			else
				set _totalPaginas= floor((_totalRegistros / tamanioPagina) + 1);                
            end if;
			
            set _offset = tamanioPagina * (numeroPagina - 1);                      
						
			select *, _totalRegistros as 'TotalRegistros', _totalPaginas as 'TotalPaginas'
			from Persona			
            order by nombre
            limit _offset, tamanioPagina;
        end;
	end if;
end
//
delimiter ;

call ObtenDatosPaginados(1,10);

PaginacionMySql