Scripts archivos - El Rincón de Cesar

Paginado de Consulta en SQL Server

7 de abril de 20211 de noviembre de 2021 Cesar Perez

Buenos dias,

Turno de otro script de SQL Server, con el que podemos paginar los resultados de una sentencia select, es muy sencillo de usar, para ello necesitamos dos variables:

DECLARE @Pagina AS INT = 1;
DECLARE @SizePagina AS INT = 100;

1 2	DECLARE @Pagina AS INT = 1; DECLARE @SizePagina AS INT = 100;

Con la variable @Pagina, estamos indicando el numero de pagina que queremos consultar. Y la variable @SizePagina nos indica el tamaño de la pagina, es decir, el numero de registros por pagina.

Con esta sentencia tendriamos el resultado:

SELECT *
FROM tbTabla
ORDER BY Campos
OFFSET (@Pagina- 1) * @SizePagina ROWS FETCH NEXT @SizePagina ROWS ONLY;

SELECT *

FROM tbTabla

ORDER BY Campos

OFFSET (@Pagina- 1) * @SizePagina ROWS FETCH NEXT @SizePagina ROWS ONLY;

Sencillo pero muy practico, espero que os valga.

Objetos modificados en SQL Server

7 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@s,

Os dejo por aqui otro script muy bueno para saber que objetos de la base de datos se han modificado en 10 dias atras.

SELECT 
   name AS object_name,
   SCHEMA_NAME(schema_id) AS schema_name,
   type_desc,
   create_date,
   modify_date
FROM 
   sys.objects
WHERE 
   modify_date > GETDATE() - 10
ORDER BY 
   modify_date;

SELECT

name AS object_name,

SCHEMA_NAME(schema_id) AS schema_name,

type_desc,

create_date,

modify_date

FROM

sys.objects

WHERE

modify_date > GETDATE() - 10

ORDER BY

modify_date;

Si quieres tener otro rango de dias, simplemente cambia del script el valor de 10 por el numero de dias que quieras mostrar.

Informacion Registros y Tamaño de Tablas en SQL Server

6 de abril de 20211 de noviembre de 2021 Cesar Perez

Buenos dias a tod@os,

Hoy os traigo otro pequeño script para SQL Server, con el que podreis sacar bastante informacion de tablas, tales como tamaños, numero de registros, etc…

SELECT
   t.NAME AS NombreTabla,
   s.Name AS Esquema,
   p.rows AS NumFilas,
   SUM(a.total_pages) * 8 AS EspacioTotal_KB,
   SUM(a.used_pages) * 8 AS EspacioUsado_KB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacioNoUsado_KB
FROM
   sys.tables t
     INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
     INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
   t.is_ms_shipped = 0
     AND i.OBJECT_ID > 255
GROUP BY
   t.Name, s.Name, p.Rows
ORDER BY
   p.rows desc, t.NAME, s.Name desc

SELECT

t.NAME AS NombreTabla,

s.Name AS Esquema,

p.rows AS NumFilas,

SUM(a.total_pages) * 8 AS EspacioTotal_KB,

SUM(a.used_pages) * 8 AS EspacioUsado_KB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacioNoUsado_KB

FROM

sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

p.rows desc, t.NAME, s.Name desc

El resultado de esta consulta seria algo como esto:

Como veis se puede obtener dicha informacion de forma rapida, os recomiendo que mires las tablas de las que se esta estrayendo la informacion, ya que podeis obtener mucha mas.

SPLIT en SQL Server

6 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@os,

Momento de traeros otro pequeño script, que os puede venir muy bien a la hora de devolver en modo registros los valores de un campo.

Para poder realizar esta tarea, lo primero que hay que revisar, es la version del servidor SQL y preparar la base de datos, ya que para que la funcion que os traigo “STRING_SPLIT” funcione, el servidor de SQL debe de ser una version SQL Server 2016 o superior.

En el caso de que sea la version que tengas, la compatibilidad de la base de datos, debe de ser la 130, te dejo aqui un pequeño script para poder revisarla y en el caso de tener que cambiarla tambien te pongo el script de actualización.

Averiguar que compatibilidad tiene la base de datos, simplemente cambia “NombreDeBaseDatos”, por el nombre de tu base de datos:

SELECT compatibility_level
FROM sys.databases WHERE name = 'NombreDeBaseDatos';

1 2	SELECT compatibility_level FROM sys.databases WHERE name = 'NombreDeBaseDatos';

Actualizar la compatibilidad de la base de datos, simplemente cambia “NombreDeBaseDatos”, por el nombre de tu base de datos:

ALTER DATABASE NombreDeBaseDatos
SET COMPATIBILITY_LEVEL = 130;
GO

ALTER DATABASE NombreDeBaseDatos

SET COMPATIBILITY_LEVEL = 130;

Y por ultimo, os dejo la sentencia que transformara los valores de un campo en registros, para ello tenemos que establecer el valor por el que va a separar la informacion, en este caso “;”

SELECT value
FROM tbTabla
CROSS APPLY STRING_SPLIT(CampoTabla, ';')

SELECT value

FROM tbTabla

CROSS APPLY STRING_SPLIT(CampoTabla, ';')

Espero que te valga, a mi me salvo en alguna…

Encriptar cadena en MD5 en SQL Server

5 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola de nuevo,

Otra pequeña aportación, en este caso, para poder generar un MD5 directamente en SQL Server, para ello con esta simple linea podremos tener el resultado, solo tienes que sustituir tu cadena donde pone “elrincondecesar.com”. Como ves es muy sencilla, la funcion de sql fn_varbintohexstr, nos permite obtenerla con esa simple linea.

SELECT SUBSTRING(
            master.dbo.fn_varbintohexstr(HashBytes('MD5','elrincondecesar.com')),3,32)

--Cadena obtenida: 694a6f3e6ec1e316422b496f995a10dc

SELECT SUBSTRING(

master.dbo.fn_varbintohexstr(HashBytes('MD5','elrincondecesar.com')),3,32)

--Cadena obtenida: 694a6f3e6ec1e316422b496f995a10dc

Si quieres ver como se hace en .NET, aqui te pongo el ejemplo que pusimos hace unos dias.

Saludos,

Convertir Filas en Columnas

5 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@s,

En esta ocasión os voy a traer un pequeño script el cual es muy util para poder tener en una columna, los valores de los diferentes registros.

La tabla de origen tiene estos registros.

Ejecutamos el siguiente script:

SELECT CAST(';' AS NVARCHAR(10)) + STUFF
((SELECT CAST(';' AS NVARCHAR(10)) + CAST(tb.ColumnaValor AS NVARCHAR(25))
	FROM            tbValoresFila tb
	ORDER BY tb.ColumnaValor FOR XML PATH('')), 1, 1, '') + CAST(';' AS NVARCHAR(10))

SELECT CAST(';' AS NVARCHAR(10)) + STUFF

((SELECT CAST(';' AS NVARCHAR(10)) + CAST(tb.ColumnaValor AS NVARCHAR(25))

FROM tbValoresFila tb

ORDER BY tb.ColumnaValor FOR XML PATH('')), 1, 1, '') + CAST(';' AS NVARCHAR(10))

El resultado seria el siguiente:

Como veis puede ser muy util, cuando necesitemos tener los valores de varios registros en una sola columna.

Buscar cadena en objetos SQL Server

1 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@s, en esta ocasión ponemos a vuestra disposición un script muy útil para buscar una cadena de texto dentro de todos los objetos de una base de datos de SQL Server, para ello, solo hay que ejecutar el siguiente script:

--Variable que contentendrá el texto a buscar, nombre de una tabla, columna, etc...
DECLARE @SearchText AS VARCHAR(100)
SET @SearchText = 'texto a buscar'

--Consulta de los procedimeintos que contienen dicho texto
SELECT DISTINCT o.id AS 'ID Objeto', USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Nombre Objeto',
	CASE
	WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
	THEN 'Replication stored procedure'
	WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
	THEN 'Extended stored procedure'
	WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
	THEN 'Stored Procedure'
	WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
	THEN 'Trigger'
	WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
	THEN 'Table-valued function'
	WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
	THEN 'Scalar-valued function'
	WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
	THEN 'Inline function'
	WHEN OBJECTPROPERTY(c.id, 'IsView') = 1
	THEN 'View'
	END AS 'Tipo Objeto', m.definition AS 'SCRIPT'
FROM syscomments c
	INNER JOIN
	sysobjects o
	ON c.id = o.id
	INNER JOIN sys.sql_modules m
	ON m.object_id = o.id
WHERE c.text LIKE '%'+@SearchText+'%' AND
	encrypted = 0 AND
	( OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
	OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
	OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
	OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
	OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
	OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
	OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 OR
	OBJECTPROPERTY(c.id, 'IsView') = 1 )
ORDER BY 'Tipo Objeto', 'Nombre Objeto

--Variable que contentendrá el texto a buscar, nombre de una tabla, columna, etc...

DECLARE @SearchText AS VARCHAR(100)

SET @SearchText = 'texto a buscar'

--Consulta de los procedimeintos que contienen dicho texto

SELECT DISTINCT o.id AS 'ID Objeto', USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Nombre Objeto',

CASE

WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1

THEN 'Replication stored procedure'

WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1

THEN 'Extended stored procedure'

WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1

THEN 'Stored Procedure'

WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1

THEN 'Trigger'

WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1

THEN 'Table-valued function'

WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1

THEN 'Scalar-valued function'

WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1

THEN 'Inline function'

WHEN OBJECTPROPERTY(c.id, 'IsView') = 1

THEN 'View'

END AS 'Tipo Objeto', m.definition AS 'SCRIPT'

FROM syscomments c

INNER JOIN

sysobjects o

ON c.id = o.id

INNER JOIN sys.sql_modules m

ON m.object_id = o.id

WHERE c.text LIKE '%'+@SearchText+'%' AND

encrypted = 0 AND

( OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR

OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR

OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR

OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR

OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR

OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR

OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 OR

OBJECTPROPERTY(c.id, 'IsView') = 1 )

ORDER BY 'Tipo Objeto', 'Nombre Objeto

Hay que sustituir la cadena que pone “texto a buscar” por el texto que queremos encontrar, y al ejecutar, nos mostrara un listado con todas las coincidencias indicando el tipo de objeto en el que ha encontrado la coincidencia.

Espero que os valga para algo, a mi me ha sacado de algún apuro.

Buscar Triggers en Base de datos

1 de abril de 20211 de noviembre de 2021 Cesar Perez

Hola a tod@s, aquí os traigo un pequeño script de SQL Server el cual nos permite buscar en toda la base de datos los triggers (disparadores) generados en las diferentes tablas, para ello simplemente hay que ejecutar el siguiente script.

SELECT e.name AS [ESQUEMA], o.name AS [TABLA], t.name AS [TRIGGERS]
FROM sys.triggers AS t inner join sys.objects AS o ON 
	(t.parent_id = o.object_id) inner join sys.schemas AS e ON 
		(o.schema_id = e.schema_id)
ORDER BY o.name, e.name, t.name

SELECT e.name AS [ESQUEMA], o.name AS [TABLA], t.name AS [TRIGGERS]

FROM sys.triggers AS t inner join sys.objects AS o ON

(t.parent_id = o.object_id) inner join sys.schemas AS e ON

(o.schema_id = e.schema_id)

ORDER BY o.name, e.name, t.name

La información realmente se puede encontrar en la tabla sys.triggers, lo que estamos haciendo es unirla con la tabla sys.objects para sacar los nombres de los objetos y a su vez, unirla con sys.schemas para sacar los esquemas afectados en el caso que los tuviésemos.

Nos es obligatorio unir las tres tablas pero si lo hacemos tendríamos un scripts mas completo.

Espero que os sea de utilidad.