Многое из того, что мы делаем, зависит от знания, которым мы обладаем. Если мы осознаем, что можно сделать, только тогда мы сможем принимать более разумные и эффективные решения. Вот почему всегда полезно иметь в своем кармане полезные советы и подсказки. Этот принцип применяется везде, в том числе и для разработчиков MS-SQL.
В этой статье я хотел бы поделиться несколькими сценариями SQL, которые оказались очень полезными для моей повседневной работы в качестве разработчика SQL. Я представлю краткий сценарий о том, где каждый из этих сценариев может использоваться вместе со сценариями ниже.
Примечание. Прежде чем воспользоваться преимуществами этих сценариев, настоятельно рекомендуется сначала запустить все предоставленные сценарии в тестовой среде, прежде чем запускать их в базе данных в реальном времени для обеспечения безопасности.
1. Поиск текста внутри всех процедур SQL
Можем ли мы представить жизнь без Control-F в современном мире? Или жизнь без поисковых систем! Ужасно, не правда ли? Теперь представьте, что у вас есть 20-30 sql процедур в вашей базе данных, и вам нужно найти процедуру, которая содержит определенное слово.
Определенно один из способов сделать это — открывать каждую процедуру по одному и выполнять Control-F внутри процедуры. Но это ручное, повторяющееся и скучное. Итак, вот быстрый скрипт, который позволяет вам достичь этого.
|
1
2
3
4
5
|
SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like ‘%search_text%’
|
2. Сравните количество строк в таблицах из двух разных баз данных с одинаковой схемой
Если у вас большая база данных и источником данных для вашей базы данных является какой-то ETL-процесс (извлечение, преобразование, загрузка), который выполняется ежедневно, этот следующий сценарий для вас.
Скажем, у вас есть сценарии, которые запускаются ежедневно для извлечения данных в вашу базу данных, и этот процесс занимает около пяти часов каждый день. Когда вы начнете более глубоко изучать этот процесс, вы обнаружите некоторые области, в которых вы можете оптимизировать сценарий, чтобы завершить задачу менее чем за четыре часа.
Вы хотели бы попробовать эту оптимизацию, но, поскольку у вас уже есть текущая реализация на производственном сервере, логичным будет попробовать оптимизированный процесс в отдельной базе данных, которую вы будете реплицировать с использованием существующей базы данных.
Теперь, когда все будет готово, вы запустите оба ETL-процесса и сравните извлеченные данные. Если у вас есть база данных с множеством таблиц, это сравнение может занять довольно много времени. Итак, вот быстрый сценарий, который облегчает этот процесс.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
use YourDatabase_1
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)
EXEC sp_MSForEachTable @command1=’INSERT #counts (table_name, row_count) SELECT »?», COUNT(*) FROM ?’
use YourDatabase_2
CREATE TABLE #counts_2
(
table_name varchar(255),
row_count int
)
EXEC sp_MSForEachTable @command1=’INSERT #counts_2 (table_name, row_count) SELECT »?», COUNT(*) FROM ?’
SELECT a.table_name,
a.row_count as [Counts from regular run],
b.row_count as [Counts from mod scripts],
a.row_count — b.row_count as [difference]
FROM #counts a
inner join #counts_2 b on a.table_name = b.table_name
where a.row_count <> b.row_count
ORDER BY a.table_name, a.row_count DESC
|
3. Резервное копирование нескольких баз данных одновременно
В любой ИТ-компании первое, что должен сделать недавно нанятый программист (или SQL-разработчик), прежде чем писать свой первый SQL-запрос, — это купить страховку рабочей версии производственной базы данных, то есть сделать резервную копию.
Этот единственный процесс создания резервной копии и работы с версией резервной копии дает вам свободу выполнять и практиковать любые виды преобразования данных, поскольку гарантирует, что даже если вы удалите данные клиента компании, их можно будет восстановить. На самом деле, не только новые сотрудники, но даже ветераны из той же ИТ-компании никогда не выполняют преобразование данных без создания резервных копий.
Хотя резервное копирование баз данных в SQL Server не является сложной задачей, оно
определенно занимает много времени, особенно когда вам нужно выполнить резервное копирование сразу нескольких баз данных. Так что следующий скрипт довольно удобен для этой цели.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name
— specify database backup directory
SET @path = ‘E:\\Sovit\_BackupFolder\’
exec master.dbo.xp_create_subdir @path
— specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN (‘DB_1′,’DB_2′,’DB_3’,
‘DB_4′,’DB_5′,’DB_6’) — only these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
4. Сократите несколько журналов базы данных одновременно
Каждая база данных SQL Server имеет журнал транзакций, в котором записываются все транзакции и изменения базы данных, сделанные каждой транзакцией. Журнал транзакций является критическим компонентом базы данных, и в случае сбоя системы может потребоваться журнал транзакций, чтобы вернуть вашу базу данных в согласованное состояние.
Однако, когда количество транзакций начинает увеличиваться, доступность пространства начинает становиться главной проблемой. К счастью, SQL Server позволяет вам освободить избыточное пространство за счет уменьшения размера журнала транзакций.
Хотя вы можете сжимать файлы журналов вручную, по одному, используя предоставленный пользовательский интерфейс, у кого есть время, чтобы сделать это вручную? Следующий скрипт можно использовать для быстрого сжатия нескольких файлов журнала базы данных.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
DECLARE @logName as nvarchar(50)
DECLARE @databaseID as int
DECLARE db_cursor CURSOR FOR
SELECT TOP 10 name,database_id — only 10 but you can choose any number
FROM sys.master_Files WHERE physical_name like ‘%.ldf’
and physical_name not like ‘C:\%’ — specify your database paths
and name not in (‘mastlog’) — any database logs that you would like to exclude
ORDER BY size DESC
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @logName , @databaseID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @databaseName as nvarchar(50)
SET @databaseName = DB_NAME(@databaseID)
DECLARE @tsql nvarchar(300)
SET @tsql=’USE [‘+@databaseName+’] ALTER DATABASE [‘+@databaseName+’] set recovery simple DBCC SHRINKFILE (‘+@logName+’ , 1)’
EXEC(@tsql)
FETCH NEXT FROM db_cursor INTO @logName , @databaseID
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
5. Ограничьте подключение к базе данных, установив однопользовательский режим
Однопользовательский режим указывает, что только один пользователь одновременно может получить доступ к базе данных и обычно используется для действий по обслуживанию. В основном, если другие пользователи подключаются к базе данных в то время, когда вы устанавливаете базу данных в однопользовательский режим, их подключения к базе данных будут закрыты без предупреждения.
Это весьма полезно в тех случаях, когда вам нужно восстановить базу данных до версии с определенного момента времени или вам нужно предотвратить возможные изменения любых других процессов, обращающихся к базе данных.
|
01
02
03
04
05
06
07
08
09
10
11
12
|
USE master;
GO
ALTER DATABASE YourDatabaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE YourDatabaseName
SET READ_ONLY;
GO
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO
|
6. Строковая функция в SQL для генерации динамических текстов
Многие языки программирования позволяют вставлять значения в строковые тексты, что очень полезно при генерации динамических строковых текстов. Поскольку SQL не предоставляет такой функции по умолчанию, здесь есть быстрое решение. Используя функцию ниже, любое количество текстов может быть динамически вставлено в текстовые строки.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
—Example Usage
—declare @test varchar(400)
—select @test = [dbo].[FN_SPRINTF] (‘I am %s and you are %s’, ‘1,0’, ‘,’) —param separator ‘,’
—print @test — result: I am 1 and you are 0
—select @test = [dbo].[FN_SPRINTF] (‘I am %s and you are %s’, ‘1#0’, ‘#’) —param separator ‘,’
—print @test — result: I am 1 and you are 0
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— AUTHOR: <SOVIT POUDEL>
— =============================================
CREATE FUNCTION DBO.FN_SPRINTF
(
@STRING VARCHAR(MAX),
@PARAMS VARCHAR(MAX),
@PARAM_SEPARATOR CHAR(1) = ‘,’
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @P VARCHAR(MAX)
DECLARE @PARAM_LEN INT
SET @PARAMS = @PARAMS + @PARAM_SEPARATOR
SET @PARAM_LEN = LEN(@PARAMS)
WHILE NOT @PARAMS = »
BEGIN
SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)
SET @STRING = STUFF(@STRING, CHARINDEX(‘%S’, @STRING), 2, @P)
SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
END
RETURN @STRING
END
|
7. Печать определений столбцов таблицы
При сравнении нескольких баз данных, имеющих сходные схемы, нужно взглянуть на детали столбцов таблицы. Определения столбцов (типы данных, обнуляемые?) Так же важны, как и названия самих столбцов.
Теперь для баз данных, имеющих много таблиц и таблиц, имеющих много столбцов, может потребоваться некоторое время, чтобы сравнить каждый столбец вручную со столбцом из другой таблицы другой базы данных. Следующий скрипт может быть точно использован для автоматизации этого самого процесса, так как он печатает определения всех таблиц для данной базы данных.
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SELECT
sh.name+’.’+o.name AS ObjectName,
s.name as ColumnName
,CASE
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+’)’
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+’)’
WHEN t.name IN (‘numeric’) THEN t.name+'(‘+CONVERT(varchar(10),s.precision)+’,’+CONVERT(varchar(10),s.scale)+’)’
ELSE t.name
END AS DataType
,CASE
WHEN s.is_nullable=1 THEN ‘NULL’
ELSE ‘NOT NULL’
END AS Nullable
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
WHERE O.name IN
(select table_name from information_schema.tables)
ORDER BY sh.name+’.’+o.name,s.column_id
|
Вывод
В этой статье мы рассмотрели семь полезных скриптов, которые могут сократить тонны кропотливой и кропотливой работы и повысить общую эффективность для разработчиков SQL. Мы также рассмотрели различные сценарии, в которых эти сценарии могут быть реализованы.
Если вы ищете еще больше SQL-скриптов для изучения (или использования), не стесняйтесь посмотреть, что у нас есть на CodeCanyon .
Как только вы начнете изучать эти сценарии, вы наверняка начнете определять множество других сценариев, в которых эти сценарии можно эффективно использовать.
Удачи!