TSQL Archive

15 Feb 2018

SQL to get counts by statuses and completeness

SQL to get counts by statuses and completeness Recently I came across a need to get the counts of result by statuses as well as completeness – i.e. getting positive result (pass) in all cases. I quickly
2 Dec 2013

Rebuild all index in a database

Several times my colleagues asked me about rebuilding all indices in a database. While there are several solution available, I like to use the one below. Please note that sp_MSforeachtable is undocumented but internal so we can
29 Nov 2013

Finding the Nth highest value in a table

I recently was asked by a colleague about finding the Nth highest value in a table. For example if there is an employee table then finding the 10th highest salary or something similar. I thought about it
2 Oct 2013

SQL Server Fun – Averages with Rollup

Here is tsql way of getting averages and also rolling up. Below example is using adventureworks database that is available with SQL server. It is getting average pay rate by each department and also overall by the
21 Sep 2013

SQL Server FUN – Split received string using XML

Several time, we have a need to split the string based on a delimiter, for instance to handle multiple input filter parameters etc. Most of the time we end up using a user defined function that accepts
21 Sep 2013

SQL Server FUN – Identity insert

Insert in identity column for a table Sometimes there is a need to insert data into a table that has identity column. Example when you would like to restore a table from a backup including the same
29 Aug 2013

Search Database for Keywords or across all tables

/* Visit my blog http://exuberantindia.com/ */ CREATE PROC dbo.KeywordSearchDB ( @SearchStr nvarchar(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET  @TableName = ” SET
9 May 2013

SQL Server – Use T-SQL to delete files older than X days

Sometimes there is a need to delete backup files via tSQL. Here is a script that can do the trick. — -d represents number of days, – 2 represents file older than 2 days set the parameter