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