Useful SQL Queries and Projects
Useful SQL Queries and Projects
Join the DZone community and get the full member experience.
Join For FreeDownload "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.
In this article, I will be telling example projects and queries which may be useful in order to automatize and reduce the time spent of operational jobs on database.
There are a lot of operational jobs on database in data-centric applications. These operational jobs may depend on number of databases, size of data, organizational structure. Let’s think about it:
- If you as firm have more software developments,you have more database servers (database) as the same time.You can be importing and exporting data for data synchronization between databases
- You may be deploying manually your database developments
- You may be doing text search in database objects
- You might prepare scripts insert and update statements for data that you added in database
- If there are open transactions on database, you must list it and then finish.
- You may be changing hard-coded places in all stored procedures
and much more business scenario. We have spent time such operational jobs. I think that’s problem. We shouldn’t spent time for it. What can we do to solve this problems? Let’s see:
- We can develop a console applications for specific yearly and monthly works on database
- We can prepare scripts for daily works on database
- We can use existing plug-in or programs.For instance; Redgate firm’s free tools is in here
- We can develop web or windows application that we use in all database works. You can use effectively MS Sql server SMO kütüphane
Now, we’ll review sample stored procedures, queries and projects item by item in the following. By the way, I have written stored procedures, queries and projects(C#) with transact-sql.
Scripts
1 - Search.sql
You will see select queries that you use for text searching in database objects (stored procedure, trigger, function… ). We use system tables for searching in the below and I should review system tables (really a helpful query).
-- 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 script for nested search. You can easily create stored procedure for this:
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
It works more professional and team on corporation database. They can run commands or queries (stored procedures) that can be critical.These transaction may sometimes unfinished and open transactions effects directly database’s performance.You can list open transactions with the following select query on database and then you must finish these transactions with KILL command:
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
This stored procedure preparing INSERT INTO statements for data that you inserted table. It’s really a practical stored procedure when you need deploy data to different environments’ database. You will also find it in the below:
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'''
Projects
You as firm have a more software environment (DEV-UAT-PREPORD). You have to change commonly used table’s name and changed as result. You have to update stored procedures, views, triggers that used this table as the same time. This is a really bothersome work.it’s not reason doing particularly this work. I dedicated make a console application for this.I want to do automatically this and i developed this project. I used SQL Server Management Objects (SMO) for database access. You can probably find Microsoft.SqlServer.Smo.dll in the directory ‘C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll‘
visit here for more detail http://technet.microsoft.com/en-us/library/ms162557.aspxa
2 – Db.JunkFinder
I designed this project as a console application. It finds temporary or junk database objects (stored procedure, table, trigger, view, function) that we generated on database and then export it as a list. If you want after testing, you can delete these objects. Developer teams especially should do this types of jobs and manage process on corporation database. This work increases database performance.
You can access github and gist pages by clicking on the title. That’s all for now. Have a good coding
Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}