Category Archives: T-SQL

How to analyze SQL Server database performance using T-SQL

The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of … Continue reading

Posted in SQL Server 2017, sysfiles, T-SQL | Leave a comment

SQL Server 2016 enhancements – Truncate Table and Table Partitioning

The idea behind this article is to discuss the importance and the implication of SQL Partition and understand the truncate command partitioning enhancements in SQL 2016 One of the biggest challenges for a DBA is to identify the right candidate … Continue reading

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

SQL Server 2016: JSON integration

Abstract This article helps to explain the application of JSON data and the use of new SQL 2016 constructs that enable an integration of JSON data into a relational schema. The idea behind this article is to list as many … Continue reading

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

SQL Server Operations: Monitoring Disk Space with WMI and OLE Automation Objects

Abstract This article talks about various SQL methods to fetch the disk usage details and use of T-SQL in conjunction with invoking OLE automation procedures and WMI  class libraries using SQL Windows Shell interface. The step by step  details about … Continue reading

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

Simple Methods to Recover master.mdf File Password

Overview MS SQL Server is the relational database management system used to store information in form of tables, views, indexes, triggers, constraints and stored procedures. Master Database File (MDF) is the main database file with the file extension .mdf used … Continue reading

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

Find failed SQL Job in last 24 Hours using Powershell

This post is based on the request to get failed SQL jobs in last 24 hours and output should be displayed in a HTML format. The verification of SQL job steps code is taken from the below blog. http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/01/29/find-failed-sql-jobs-with-powershell/ The … Continue reading

Posted in PowerShell, SQL, T-SQL | Tagged , | 12 Comments

SQL to generate Asset Information – Configuration Manager SCCM 2012

SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName,               s.Operating_System_Name_and0 AS OSName,               pr.Name0 AS ProcessorTypeSpeed,               pr.Manufacturer0 Manufacturer,              pr.NumberOfCores0 Cores,              pr.NumberOfLogicalProcessors0 LgicalProcessorCount,              case when pr.DataWidth0=64 then ’64 bit’ else ’32 bit’ end DataWidth,              m.TotalPhysicalMemory0/1024.00 AS MemoryMB,               GS1.TotalVirtualMemorySize0 VirtualMemory,              GS1.TotalVisibleMemorySize0 VisibleMemory,              ip.IPAddress0,               T1.COL AS TotalDriveSize,              LastBootUpTime0,              DATEDIFF(Day,GS1.LastBootUpTime0, GETDATE()) AS [Days since last boot]            FROM v_R_System_Valid s          INNER JOIN v_GS_PROCESSOR pr ON s.ResourceID = pr.ResourceID         INNER JOIN v_GS_COMPUTER_SYSTEM gs ON s.ResourceID = gs.ResourceID          INNER JOIN v_GS_NETWORK_ADAPTER ON s.ResourceID = v_GS_NETWORK_ADAPTER.ResourceID          INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID         INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION ip ON s.ResourceID = ip.ResourceID        — INNER JOIN v_GS_LOGICAL_DISK AS ld ON s.ResourceID = ld.ResourceID          INNER JOIN          ( SELECT RESOURCENAME,          col  FROM   (           SELECT DISTINCT TAB.Netbios_Name0 RESOURCENAME,               (               SELECT COL.deviceid0 +’ ‘+ cast(COL.Size0/1024.00 AS varchar(20))+’ ‘              FROM v_GS_LOGICAL_DISK COL                WHERE                    COL.ResourceID = TAB.ResourceID AND COL.DriveType0=3               FOR XML PATH (”)               ) COL   FROM v_R_System_Valid TAB    )T    where T.COL is NOT NULL    ) T1 on T1.RESOURCENAME=s.Netbios_Name0         INNER JOIN V_GS_OPERATING_SYSTEM GS1 on GS1.ResourceID=s.ResourceID  WHERE   … Continue reading

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

T-SQL – Find Number of Rows of Database(s) tables Using Undocumented Procedure – sp_MSforeachdb and sp_MSforeachtable

Originally posted on Prashanth Jayaram:
On Technet forum, Op was looking for a way to get table cardinality for all databases in an instance.  There are many ways to get the result. This post gives you an idea and SQL in which…

Posted in SQL, T-SQL | Leave a comment

T-SQL :- How to Search String in all Stored Procedures across All User Defined Databases

There are many different ways to accomplish this tasks. Download Link https://gallery.technet.microsoft.com/T-SQL-How-to-Search-String-a1704fc6 The below examples uses undocumented sp’s to loop through all the user defined database to search the SP’s using given string CREATE TABLE #ProcSearch (databaseName VARCHAR(100),ROUTINE_CATALOG varchar(50),ProcName VARCHAR(128),RoutineType varchar(100),CreateDate … Continue reading

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

T-SQL – Query to get Distinct,Sorted, Comma Separated values in to a variable

CREATE TABLE #temp(name char(3)) INSERT INTO #temp VALUES (‘CD’) ,(‘AB’) ,(‘LM’) ,(‘BC’) ,(‘GH’) ,(‘KJ’) ,(‘AB’) DECLARE @cols AS NVARCHAR(MAX); SELECT @COLS = substring(list, 1, len(list) – 1) FROM (SELECT list = (SELECT DISTINCT name + ‘,’ FROM #temp ORDER BY … Continue reading

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