-
Personal Links
Verified Services
Top 30 PowerShell bloggers of 2018
https://www.sqlshack.com/top-50-powershell-bloggers-of-2018/#Prashanth%20JayaramMy Blog is ranked amongst the Top 50 SQL blogs worldwide
http://blog.feedspot.com/sql_blogs/DZone MVB
Microsoft
Category Archives: T-SQL
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
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
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
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 Find SQL Failed Jobs, PowerShell to find failed SQL Jobs
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
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 – 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
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 @whereand, get record count of all database into temp table, rebuild all indexes, sp_msforeach like clause, sp_MSForeachdb, sp_MSforeachdb and sp_MSforeachtable examples, sp_msforeachtable, sp_msforeachtable if clause, SQL, total record count, Total rows
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 calendar dates, Calendar Example, choose, choose example, DATEPART, Display Weekend, find weekends, T-sQL calendar dates, Table dataType
2 Comments
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 →