7 útiles scripts SQL para desarrolladores SQL

Muchas de las cosas que hacemos dependen del conocimiento que tenemos. Si somos conscientes de lo que se puede hacer, sólo entonces podremos tomar decisiones más inteligentes y eficaces. Es por eso que siempre es bueno tener a la mano consejos y trucos rápidos en el bolsillo. Este principio se aplica en todas partes, incluso para los desarrolladores de MS-SQL.

Mediante este artículo, me gustaría compartir algunos scripts SQL que demostraron ser muy útiles para mi trabajo diario como desarrollador SQL. A continuación, presentaré un breve escenario sobre dónde se puede usar cada uno de estos scripts junto con los scripts.

Nota: Antes de aprovechar los beneficios de estos scripts, se recomienda encarecidamente que todos los scripts proporcionados se ejecuten en un entorno de prueba antes de ejecutarlos en una base de datos en tiempo real para garantizar la seguridad.

1. Buscar texto dentro de todos los procedimientos SQL

¿Podemos imaginar la vida sin Control-F en el mundo actual? ¡O una vida sin motores de búsqueda! Horrible, ¿no? Ahora imagina que tienes 20-30 procedimientos sql en tu base de datos y necesitas encontrar el procedimiento que contiene una palabra determinada.

Definitivamente una manera de hacerlo es abriendo cada procedimiento uno a la vez y haciendo un Control-F dentro del procedimiento. Pero esto es manual, repetitivo y aburrido. Entonces, este es un script rápido que te permite lograrlo.

1

SELECT DISTINCT o.name AS Object_Name,o.type_desc

2

FROM sys.sql_modules m

3

INNER JOIN sys.objects o

4

ON m.object_id=o.object_id

5

WHERE m.definition Like '%search_text%'

2. Comparar recuentos de filas en tablas de dos bases de datos diferentes con el mismo esquema

Si tienes una base de datos grande y la fuente de datos de la base de datos es un proceso ETL (extraer, transformar, cargar) que se ejecuta a diario, este siguiente script es para ti.

Supongamos que tienes scripts que se ejecutan a diario para extraer datos en la base de datos y este proceso tarda unas cinco horas al día. A medida que comienzas a profundizar en este proceso, encontrarás algunas áreas en las que puedes optimizar el script para terminar la tarea en menos de cuatro horas.

Te gustaría ensayar esta optimización, pero como ya tienes la implementación actual en un servidor de producción, lo lógico es probar el proceso optimizado en una base de datos independiente, que se replicaría mediante la base de datos existente.

Ahora, una vez listo, ejecutarás ambos procesos ETL y compararás los datos extraídos. Si tienes una base de datos con muchas tablas, esta comparación puede tardar bastante tiempo. Entonces, este es un script rápido que facilita este proceso.

1

use YourDatabase_1

2

CREATE TABLE #counts

3

(

4

table_name varchar(255),

5

row_count int

6

)

7

8

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

9

10

use YourDatabase_2

11

CREATE TABLE #counts_2

12

(

13

table_name varchar(255),

14

row_count int

15

)

16

17

EXEC sp_MSForEachTable @command1='INSERT #counts_2 (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

18

19

SELECT a.table_name,

20

a.row_count as [Counts from regular run],

21

b.row_count as [Counts from mod scripts],

22

a.row_count - b.row_count as [difference]

23

FROM #counts a

24

inner join #counts_2 b on a.table_name = b.table_name

25

where a.row_count <> b.row_count

26

ORDER BY a.table_name, a.row_count DESC

3. Copia de seguridad de varias bases de datos a la vez

En cualquier empresa de TI, lo primero que un programador recién contratado (o desarrollador de sql) tiene que hacer antes de escribir su primera consulta SQL es comprar un seguro de la versión funcional de la base de datos de producción, es decir, hacer una copia de seguridad.

Este solo acto de crear una copia de seguridad y trabajar con la versión de copia de seguridad te da la libertad de realizar y practicar cualquier tipo de transformación de datos, ya que garantiza que incluso si eliminas los datos del cliente de la empresa, se pueden recuperar. De hecho, no solo los nuevos empleados, sino incluso los veteranos de la misma empresa de TI, nunca realizan ninguna transformación de datos sin crear copias de seguridad.

Aunque realizar copias de seguridad de bases de datos en SQL Server no es una tarea difícil, definitivamente lleva mucho tiempo, especialmente cuando necesitas hacer una copia de seguridad de muchas bases de datos a la vez. Entonces el siguiente script es bastante útil para este propósito.

1

DECLARE @name VARCHAR(50) -- database name

2

DECLARE @path VARCHAR(256) -- path for backup files

3

DECLARE @fileName VARCHAR(256) -- filename for backup

4

DECLARE @fileDate VARCHAR(20) -- used for file name

5

6

-- specify database backup directory

7

SET @path = 'E:\\Sovit\_BackupFolder\'

8

exec master.dbo.xp_create_subdir @path

9

10

-- specify filename format

11

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

12

13

DECLARE db_cursor CURSOR FOR

14

SELECT name

15

FROM master.dbo.sysdatabases

16

WHERE name IN ('DB_1','DB_2','DB_3',

17

'DB_4','DB_5','DB_6') -- only these databases

18

19

OPEN db_cursor

20

FETCH NEXT FROM db_cursor INTO @name

21

22

WHILE @@FETCH_STATUS = 0

23

BEGIN

24

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

25

BACKUP DATABASE @name TO DISK = @fileName

26

27

FETCH NEXT FROM db_cursor INTO @name  

28

END

29

30

CLOSE db_cursor

31

DEALLOCATE db_cursor

4. Reducir varios registros de base de datos a la vez

Cada base de datos de SQL Server tiene un registro de transacciones que registra todas las transacciones y las modificaciones de la base de datos realizadas por cada transacción. El registro de transacciones es un componente crítico de la base de datos y, si hay una falla en el sistema, es posible que se requiera el registro de transacciones para devolver la base de datos a un estado lógico.

Sin embargo, a medida que la cantidad de transacciones comienza a aumentar, la disponibilidad de espacio comienza a convertirse en una preocupación importante. Afortunadamente, SQL Server te permite recuperar el espacio sobrante al reducir el tamaño del registro de transacciones.

Aunque puedes reducir los archivos de registro manualmente, uno a la vez mediante la interfaz de usuario proporcionada, ¿quién tiene tiempo para hacerlo manualmente? El siguiente script se puede utilizar para reducir rápidamente varios archivos de registro de base de datos.

1

DECLARE @logName as nvarchar(50)

2

DECLARE @databaseID as int

3

4

DECLARE db_cursor CURSOR FOR 

5

SELECT TOP 10 name,database_id -- only 10 but you can choose any number

6

FROM sys.master_Files WHERE physical_name like '%.ldf'

7

and physical_name not like 'C:\%'  -- specify your database paths

8

and name not in ('mastlog') -- any database logs that you would like to exclude

9

ORDER BY size DESC

10

11

OPEN db_cursor 

12

FETCH NEXT FROM db_cursor INTO @logName , @databaseID

13

14

WHILE @@FETCH_STATUS = 0 

15

BEGIN 

16

    DECLARE @databaseName as nvarchar(50)

17

 SET @databaseName =  DB_NAME(@databaseID)

18

19

  DECLARE @tsql nvarchar(300)

20

 SET @tsql='USE ['+@databaseName+'] ALTER DATABASE ['+@databaseName+'] set recovery simple DBCC SHRINKFILE ('+@logName+' , 1)'

21

 EXEC(@tsql)

22

23

    FETCH NEXT FROM db_cursor INTO @logName , @databaseID

24

END 

25

CLOSE db_cursor

26

DEALLOCATE db_cursor

5. Restringir la conexión a la base de datos mediante la configuración del modo de usuario único

El modo de usuario único especifica que solo un usuario a la vez puede acceder a la base de datos y generalmente se usa para acciones de mantenimiento. Básicamente, si otros usuarios están conectados a la base de datos en el momento en que la configuras en modo de usuario único, sus conexiones a la base de datos se cerrarán sin previo aviso.

Esto es bastante útil en los escenarios en los que necesitas restaurar tu base de datos a la versión desde un momento determinado o necesitas evitar posibles cambios por cualquier otro proceso que acceda a la base de datos.

1

USE master;

2

GO

3

ALTER DATABASE YourDatabaseName

4

SET SINGLE_USER

5

WITH ROLLBACK IMMEDIATE;

6

GO

7

ALTER DATABASE YourDatabaseName

8

SET READ_ONLY;

9

GO

10

ALTER DATABASE YourDatabaseName

11

SET MULTI_USER;

12

GO

6. Función de cadena en SQL para generar textos dinámicos

Muchos lenguajes de programación te permiten insertar valores dentro de textos de cadena, lo que es muy útil al generar textos de cadena dinámicos. Dado que SQL no proporciona ninguna función de este tipo de forma predeterminada, esta es una solución rápida para eso. Mediante la siguiente función, cualquier número de textos se puede insertar dinámicamente dentro de los textos de cadena.

1

--Example Usage

2

--declare @test varchar(400)

3

--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','

4

--print @test -- result: I am 1 and you are 0

5

--select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator ','

6

--print @test -- result: I am 1 and you are 0

7

8

SET ANSI_NULLS ON

9

GO

10

SET QUOTED_IDENTIFIER ON

11

GO

12

-- =============================================

13

-- AUTHOR: <SOVIT POUDEL>

14

-- =============================================

15

CREATE FUNCTION DBO.FN_SPRINTF

16

(

17

@STRING VARCHAR(MAX),

18

@PARAMS VARCHAR(MAX),

19

@PARAM_SEPARATOR CHAR(1) = ','

20

)

21

RETURNS VARCHAR(MAX)

22

AS

23

BEGIN

24

25

DECLARE @P VARCHAR(MAX)

26

DECLARE @PARAM_LEN INT

27

28

SET @PARAMS = @PARAMS + @PARAM_SEPARATOR   

29

SET @PARAM_LEN = LEN(@PARAMS)

30

WHILE NOT @PARAMS = ''

31

BEGIN

32

    SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)       

33

    SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)

34

    SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)

35

END

36

RETURN @STRING

37

38

END

7. Impresión de definiciones de columnas de tablas

Al comparar varias bases de datos que tienen esquemas similares, hay que examinar los detalles de las columnas de tabla. Las definiciones de las columnas (tipos de datos, ¿anulables?) son tan vitales como el nombre de las columnas en sí.

Ahora, para las bases de datos que tienen muchas tablas y tablas que tienen muchas columnas, puede tomar bastante tiempo comparar cada columna manualmente con una columna de otra tabla de otra base de datos. El siguiente script se puede utilizar con precisión para automatizar este mismo proceso, ya que imprime las definiciones de todas las tablas para una base de datos determinada.

1

SELECT

2

sh.name+'.'+o.name AS ObjectName,

3

s.name as ColumnName

4

,CASE

5

    WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'

6

	WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'

7

	WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'

8

	ELSE t.name

9

END AS DataType

10

,CASE

11

	 WHEN s.is_nullable=1 THEN 'NULL'

12

	ELSE 'NOT NULL'

13

END AS Nullable       

14

15

FROM sys.columns s

16

INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0

17

INNER JOIN sys.objects o ON s.object_id=o.object_id

18

INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id

19

20

WHERE O.name IN

21

   (select table_name from information_schema.tables) 

22

23

ORDER BY sh.name+'.'+o.name,s.column_id

Conclusión

En este artículo, analizamos siete scripts útiles que pueden reducir toneladas de trabajo manual y laborioso y aumentar la eficiencia general para los desarrolladores de SQL. También analizamos diferentes escenarios en los que se pueden implementar estos scripts.

Si estás buscando aún más scripts SQL para estudiar (o usar), no dudes en ver lo que tenemos disponible en CodeCanyon.

Una vez que empieces a familiarizarte con estos scripts, sin duda empezarás a identificar muchos otros escenarios en los que se pueden utilizar estos scripts de forma eficaz.

¡Buena suerte!