Monthly Archives: January 2015

RIGHT() function in SQL Server

By | January 26, 2015

SQL Server – RIGHT () function Right():- This function returns the specified number of characters from the Right part of the given character string. Syntax:- Select RIGHT ( character_expression , integer_expression ) For example, the below query will return two characters from the last. Select RIGHT(‘Vivek’,2) Result ek Database Consultant with more than 11.5 years of… Read More »

LEFT() function in SQL Server

By | January 26, 2015

LEFT() function in SQL Server Left() Function  This function returns the specified number of characters from the left part of the given character string. Syntax Select LEFT ( character_expression , integer_expression )For example, the below query will return two characters from the beginning Select LEFT  (‘Vivek’,2) Result Vi Database Consultant with more than 11.5 years… 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%’ Database Consultant with more than 11.5 years… Read More »

SQL Script to find the databases size

By | January 20, 2015

Sometimes we need to find out the size of the database on a server. Below SQL Scripts can be used to find out the size of all the databases created on the server.  ;WITH DBSize (SqlServerInstanceName, DatabaseName, DatabaseSize, DBLogSize, TotalDBSize) AS (   SELECT      @@SERVERNAME SqlServerInstanceName,             db.name AS… Read More »