Category Archives: T-SQL

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

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

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 undocumented SP’s being called  “sp_MSforeachdb and … Continue reading

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

T-SQL to Display Weekends Between two Dates

Different ways to find the weekend between two given dates. The SQL requires @begindate and @endate paramteters to be entered in the below SQL Download T-SQL WeekendBetweenTwoValidDates DECLARE @beginDate Date=’20150101′, @endDate Date=’20150131′ DECLARE @Calendar Table (CalendarDate Date Primary key, IsWeekend Bit) … Continue reading

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

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