В этой статье я расскажу о примерах проектов и запросов, которые могут быть полезны для автоматизации и сокращения времени, затрачиваемого на оперативные работы с базой данных.
В приложениях, ориентированных на данные, существует много рабочих заданий в базе данных. Эти рабочие задания могут зависеть от количества баз данных, размера данных, организационной структуры. Давайте подумаем об этом:
- Если у вас как у фирмы больше разработок программного обеспечения, у вас одновременно будет больше серверов баз данных (базы данных). Вы можете импортировать и экспортировать данные для синхронизации данных между базами данных.
- Возможно, вы вручную развертываете свою базу данных
- Вы можете делать текстовый поиск в объектах базы данных
- Вы можете подготовить сценарии вставки и обновления операторов для данных, которые вы добавили в базу данных.
- Если в базе данных есть открытые транзакции, вы должны перечислить ее и затем завершить.
- Вы можете менять жестко запрограммированные места во всех хранимых процедурах
и многое другое бизнес-сценарий. Мы потратили время на такие оперативные работы. Я думаю, что это проблема. Мы не должны тратить время на это. Что мы можем сделать, чтобы решить эту проблему? Давайте посмотрим:
- Мы можем разработать консольные приложения для конкретных ежегодных и ежемесячных работ по базе данных.
- Мы можем подготовить сценарии для ежедневных работ по базе данных
- Мы можем использовать существующий плагин или программы. Например; Бесплатные инструменты фирмы Redgate находятся здесь
- Мы можем разработать веб-приложение или приложение для Windows, которое мы используем во всех работах с базами данных. Вы можете эффективно использовать MS Sql сервер SMO kütüphane
Теперь мы рассмотрим примеры хранимых процедур, запросов и проектов по пунктам ниже. Кстати, я написал хранимые процедуры, запросы и проекты (C #) с transact-sql.
Сценарии
1 — Search.sql
Вы увидите выбранные запросы, которые вы используете для поиска текста в объектах базы данных (хранимая процедура, триггер, функция…). Мы используем системные таблицы для поиска ниже, и я должен просмотреть системные таблицы (действительно полезный запрос).
-- database object contents
SELECT * FROM sys.syscomments(NOLOCK)
-- For schema names of tables
SELECT * FROM sys.schemas(NOLOCK)
-- All database objects' names
SELECT * FROM sys.all_objects(NOLOCK)
-- main select query
SELECT S.name AS SP_SCHEMA,
O.name AS SP_NAME,
C.text AS SP_TEXT
FROM sys.syscomments(NOLOCK) AS C
JOIN sys.all_objects(NOLOCK) AS O
ON C.id = O.object_id
JOIN sys.schemas AS S
ON S.schema_id = O.schema_id
WHERE O.type in ('P','FN','IF','FS','AF','X','TF','TR','PC') AND
C.text like '%' + 'ARANACAK KELIME' + '%'
T-SQL скрипт для вложенного поиска. Вы можете легко создать хранимую процедуру для этого:
DECLARE @text1 VARCHAR(MAX),
@text2 VARCHAR(MAX),
@text3 VARCHAR(MAX),
@text4 VARCHAR(MAX),
@text5 VARCHAR(MAX),
@dbname VARCHAR(64)
SET @dbname='DB_NAME'
SET @text1='TEXT1_TO_SEARCH'
SET @text2='TEXT2_TO_SEARCH'
SET @text3='TEXT3_TO_SEARCH'
SET @text4='TEXT4_TO_SEARCH'
SET @text5='TEXT5_TO_SEARCH'
DECLARE @sql VARCHAR(MAX)
SELECT @sql = ''
SELECT @sql = @sql + 'SELECT * FROM ('
SELECT @sql = @sql + 'SELECT * FROM ('
SELECT @sql = @sql + 'SELECT * FROM ('
SELECT @sql = @sql + 'SELECT * FROM ('
select @sql = @sql + 'SELECT ''' + @dbname + ''' AS db, o.name,m.definition '
select @sql = @sql + ' FROM '+@dbname+'.sys.sql_modules m '
select @sql = @sql + ' INNER JOIN '+@dbname+'..sysobjects o on m.object_id=o.id'
select @sql = @sql + ' WHERE [definition] LIKE ''%'+@text1+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text2+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text3+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text4+'%'''
SELECT @sql = @sql + ' ) X WHERE [definition] LIKE ''%'+@text5+'%'''
--PRINT @sql
execute (@sql)
3- OpenTransactionListingAndKill.sql
Он работает более профессионально и команда на базе данных корпорации. Они могут запускать команды или запросы (хранимые процедуры), которые могут быть критическими. Эти транзакции иногда могут быть незавершенными, и открытые транзакции влияют непосредственно на производительность базы данных. Вы можете перечислить открытые транзакции с помощью следующего запроса на выборку для базы данных, а затем завершить эти транзакции командой KILL. :
SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id -- We get KILL to open transaction with SPID --KILL SPID
4- INSERT.sql
Эта хранимая процедура подготавливает операторы INSERT INTO для данных, которые вы вставили в таблицу. Это действительно практичная хранимая процедура, когда вам нужно развернуть данные в базе данных разных сред. Вы также найдете его ниже:
GO
/****** Object: StoredProcedure [dbo].[INSERT] Script Date: ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--drop proc [dbo].[INSERT]
CREATE procedure [dbo].[INSERT]
(
@Query Varchar(MAX)
)
AS
SET nocount ON
DECLARE @WithStrINdex AS INT
DECLARE @WHEREStrINdex AS INT
DECLARE @INDExtouse AS INT
DECLARE @SchemaANDTAble VArchar(270)
DECLARE @Schema_name varchar(30)
DECLARE @Table_name varchar(240)
DECLARE @Condition Varchar(MAX)
SET @WithStrINdex=0
SELECT @WithStrINdex=CHARINDEX('With',@Query )
, @WHEREStrINdex=CHARINDEX('WHERE', @Query)
IF(@WithStrINdex!=0)
SELECT @INDExtouse=@WithStrINdex
ELSE
SELECT @INDExtouse=@WHEREStrINdex
SELECT @SchemaANDTAble=LEFT (@Query,@INDExtouse-1)
SELECT @SchemaANDTAble=LTRIM (RTRIM( @SchemaANDTAble))
SELECT @Schema_name= LEFT (@SchemaANDTAble, CharINdex('.',@SchemaANDTAble )-1)
, @Table_name = SUBSTRING( @SchemaANDTAble , CharINdex('.',@SchemaANDTAble )+1,LEN(@SchemaANDTAble) )
, @Condition=SUBSTRING(@Query,@WHEREStrINdex+6,LEN(@Query))--27+6
DECLARE @COLUMNS table (Row_number SmallINT , Column_Name VArchar(Max) )
DECLARE @CONDITIONS AS varchar(MAX)
DECLARE @Total_Rows AS SmallINT
DECLARE @Counter AS SmallINT
DECLARE @ComaCol AS varchar(MAX)
SELECT @ComaCol=''
SET @Counter=1
SET @CONDITIONS=''
INsert INTO @COLUMNS
SELECT Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FROM INFORMATION_SCHEMA.columns WHERE Table_schema=@Schema_name
AND table_name=@Table_name
AND Column_Name NOT IN ('FTP_PASSWORD','FTP_ENCRYPTED_PASSWORD')
SELECT @Total_Rows= Count(1) FROM @COLUMNS
SELECT @Table_name= '['+@Table_name+']'
SELECT @Schema_name='['+@Schema_name+']'
While (@Counter<=@Total_Rows )
begIN
--PRINT @Counter
SELECT @ComaCol= @ComaCol+'['+Column_Name+'],'
FROM @COLUMNS
WHERE [Row_number]=@Counter
SELECT @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+
Replace( Convert(varchar(Max),['+Column_Name+'] ) ,'''''''','''' )
+'''''''' end+'+''','''
FROM @COLUMNS
WHERE [Row_number]=@Counter
AND Column_name NOT IN ('FTP_PASSWORD','FTP_ENCRYPTED_PASSWORD')
SET @Counter=@Counter+1
End
SELECT @CONDITIONS=RIGHT(@CONDITIONS,LEN(@CONDITIONS)-2)
SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)
SELECT @ComaCol= substrINg (@ComaCol,0, len(@ComaCol) )
SELECT @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS
SELECT @CONDITIONS=@CONDITIONS+'+'+ ''')'''
--PrINt(@Condition)
SELECT @CONDITIONS= 'SELECT '+@CONDITIONS +'FROM ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' WHERE '+@Condition
--prINt(@CONDITIONS)
Exec(@CONDITIONS)
/****** let's use it ******/
EXEC [dbo].[INSERT] 'dbo.TABLE WHERE COLUMN_NAME=''TEXT'''
проектов
Вы, как фирма, имеете более программную среду ( DEV — UAT — PREPORD ). Вы должны изменить имя часто используемой таблицы и изменить в результате. Вы должны обновить хранимые процедуры, представления, триггеры, которые использовали эту таблицу одновременно. Это действительно надоедливая работа. Это не причина делать эту работу особенно. Я посвятил себя созданию консольного приложения для этого. Я хочу сделать это автоматически, и я разработал этот проект. Я использовал объекты управления SQL Server (SMO) для доступа к базе данных. Вероятно, вы можете найти файл Microsoft.SqlServer.Smo.dll в каталоге « C: \ Program Files (x86) \ Microsoft SQL Server \ 110 \ SDK \ Assemblies \ Microsoft.SqlServer.Smo.dll ».
посетите здесь для получения более подробной информации 
http://technet.microsoft.com/en-us/library/ms162557.aspx a
2 — Db.JunkFinder
Я разработал этот проект как консольное приложение. Он находит временные или ненужные объекты базы данных (хранимая процедура, таблица, триггер, представление, функция), которые мы создали в базе данных, а затем экспортирует их в виде списка. Если вы хотите после тестирования, вы можете удалить эти объекты. Команды разработчиков особенно должны выполнять эти виды работ и управлять процессами в корпоративной базе данных. Эта работа повышает производительность базы данных.
Вы можете получить доступ к страницам GitHub и Gist , нажав на заголовок. Это все на данный момент. Хорошего кодирования