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' + '%'

2-  Nested_Search.sql

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

1 - Db.ProcedureDeployer

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