Category Archives: SQL Server

This category contains all SQL related articles

Script to find the complete weeks within two given dates

By | July 10, 2015

Complete weeks within two given dates Sometimes we need to find out the complete weeks within two given dates. Below script will help in finding the complete weeks within 2 given dates. In this script, I have used below dates as exampleStartdate=2015-04-12 Enddate=2015-06-10 SET DATEFORMAT YMD if (object_id(‘tempdb..#weekdays’) is not null) drop table #weekdays Declare… Read More »

Importance of Best Practices in database programming

By | February 1, 2015

Best Practices in database programming For any programming language, just writing the code is not well enough. It should be written using the best practices. This article will try to explain the disadvantages of writing code without using Best Practices and later on how best practices can be implemented in our database code. Code written… Read More »

SQL Script to find the missing indexes

By | January 25, 2015

Script to find the missing indexes Performance tuning in SQL is important exercise and index creation is an important part of it. Sometimes base on the frequent SQL queries, we need to create some indexes or missing indexes which are enhance the query performance. Below script will help in finding the missing indexes. SELECT db_name(d.database_id) dbname , object_name(d.object_id)… Read More »

SQL Server – Convert() function

By | January 24, 2015

Convert () function in SQL Server Convert () function is used to convert an expression from one data type to another data typeSyntax for CONVERT function CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Convert functions is usually used with date/time data type to convert a date in different formats. Below examples will… Read More »

SQL Script to find the list of all the jobs failed yesterday

By | January 20, 2015

One of the important task of any DBA is to find out all the jobs which are failed yesterday. Below SQL Script can be used to find out all the jobs which are failed yesterday. SELECT DISTINCT CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS ‘Failure Date’, SUBSTRING(T2.name,1,40) AS ‘Job Name’, T1.step_id AS ‘Step_id’, T1.step_name AS ‘Step… Read More »

SQL Script to search stored procedures containing a given text

By | January 20, 2015

Sometimes we need to find out how many stored procedures contains a given text. Below SQL query can be used to find out the list of all the stored procedures which contains a  particular given text as input. SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE ‘%Given text%’