Category Archives: T-SQL

T-SQL – How to find Next Business day from a given date

There are many methods to find next business day. One common way is to use of calendar table.  This post is to get an output using T-SQL Or UDF function. Download T-SQL  NextBusinessDay The details are given below The script can be executed by … Continue reading

Posted in T-SQL | Tagged , , , , , , , | 1 Comment

SQL – Split delimited columns using XML Or UDF function

The requirement is to convert the delimited column into rows Input data and required output details are given below Eno Ename Esloc EDept 1 ABC NJ 10,20,30,40,50 Output:- Eno Ename Esloc EDept 1 ABC NJ 10 1 ABC NJ 20 … Continue reading

Posted in SQL, T-SQL | Tagged , , , , , | Leave a comment

Determine size of the file using T-SQL/Powershell/Ole Objects

There are many ways to find the filesize. This post is all about identifing the filesize using T-SQL,Instantiating OLE Objects and PowerShell. The requirement is to retrieve the size of the specified file. The return value is going to a … Continue reading

Posted in PowerShell, SQL, T-SQL | Tagged , , , , , | Leave a comment

T-SQL – Scripting Stored Procedure to Create backup copy on same database

This post is one of the request from OP. Download SQL  Backup SP in the same database The probelm statement 1:- Create a backup copy of a group of stored procedures on the same database.  Using TSQL I need to … Continue reading

Posted in SQL, T-SQL | Tagged , , , | Leave a comment

INVENTORY – SQL- SSAS – SSRS – Excel Output – Email

Inventory Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When … Continue reading

Posted in PowerShell, SQL, SSAS, SSRS, T-SQL | Tagged , , , , , , , | 2 Comments

PowerShell – Export SQL Data to Excel with Powershell

SQL Server  Export  to Excel with Powershell is very useful for all sorts of activities – ETL and reporting are some examples. This post is to Export SQL Data to Excel with Powershell.In this example, a connection is made to … Continue reading

Posted in T-SQL | Tagged , , , , | 5 Comments

T-SQL to check Data and Log Files are on same drive or not

The below query help us in finding the physical existence of data and log files are on same drive or not of all the database in a SQL Instance. ;WITH LogCTE AS  (  SELECT DISTINCT LD.Database_Name  FROM  (  SELECT DB_NAME(database_id) AS [Database_Name], LEFT(Physical_Name, 1) AS [Drive_Letter] FROM sys.master_files WHERE type_desc = ‘LOG’ AND database_id > 4  ) AS LD   INNER JOIN    sys.master_files mf     ON LD.Drive_Letter = LEFT(mf.physical_name, 1) and mf.name=LD.Database_Name  WHERE  … Continue reading

Posted in T-SQL, Uncategorized | Tagged , , | Leave a comment

SQL – Identify & Drop Duplicate Index

This post explains the simple steps to identify a duplicate indexes in all the databases.  The duplicate index are more than one indexes that differ only by it’s name but consist of identical fields internally. It generates overhead when performing updates, inserts … Continue reading

Posted in SQL, T-SQL | Tagged , , , | 4 Comments

SQL – One Query to fetch SQL Jobs Details of All SQL Version – 2000/05/08/R2

Single Query to fetch complete details of sql jobs and you can run it across different version of SQL [2000/2005/2008 and R2]. I’ve undergone dynamic query string limitation issue and hence I’m including the workaround details in the same post. … Continue reading

Posted in SQL, T-SQL | Tagged , , , , | 5 Comments

SQL – Find Database File Usage Details – 2000/05/08/R2

This post is to find the database file usage details of all databases of an instance. The query runs on all versions of sql 2000/2005/2008/R2 . The requirement is to use same query to be executed it across all version … Continue reading

Posted in SQL, T-SQL | Tagged , , , , , | Leave a comment