Category Archives: SQL

Find failed SQL Job in last 24 Hours using Powershell

Posted on March 2, 2016 by Prashanth Jayaram

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

PowerShell – Backup Individual database and delete all it’s respective files by keeping a recent file

Posted on February 29, 2016 by Prashanth Jayaram

Problem Statement The requirement is to backup individual database and after successful backup the script should retain the most recent file and delete the rest from a directory for that specific database. The below Powershell script is used to backup a … Continue reading →

Posted in PowerShell, SQL | Tagged , , | 4 Comments

Database Level Permission DECLARE @DBuser_sql VARCHAR(4000) DECLARE @DBuser_table TABLE ( DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedDatabaseRole VARCHAR(200) ) SET @DBuser_sql=’ SELECT “[?]” AS DBName,a.name AS Name, a.type_desc AS LoginType, USER_NAME(b.role_principal_id) AS AssociatedDatabaseRole FROM [?].sys.database_principals a LEFT OUTER JOIN [?].sys.database_role_members … Continue reading →

Posted on February 24, 2016 by Prashanth Jayaram | 1 Comment

Learn How to Insert Data From Stored Procedure Into Table?

Posted on December 26, 2015 by Andrew Jackson

Stored Procedures are considered most efficient way for calling and retrieving data and hence users always look forward to encapsulate logic in it so that it can be called when needed. While encapsulating data in Stored Procedure, it is also … Continue reading →

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

SQL to generate Asset Information – Configuration Manager SCCM 2012

Posted on December 11, 2015 by Prashanth Jayaram

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

Use FILEPROPERTY to find free space in all the database

Posted on August 26, 2015 by Prashanth Jayaram

The use of SpaceUsed property of the FILEPROPERTY function gives how much space is used also we can derive lot of other attributes of it such as free space and percentage of free space. For other versions of SQL you … Continue reading →

Posted in databases_files, FileProperty, sp_msforeachDB, SQL, sysfiles | Tagged , | Leave a comment

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

Posted on March 17, 2015 by Prashanth Jayaram

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 get the Financial Quarter details of a date field

Posted on March 11, 2015 by Prashanth Jayaram

declare @table table ( [Paid Date] date ) insert into @table values(‘20150102’),(‘20150512’),(‘20150830’),(‘20151231’),(‘20141230’) ;WITH Quarters AS ( SELECT Q = ‘Q1’, MonthBegin = 1, MonthEnd = 3 UNION SELECT Q = ‘Q2’, MonthBegin = 4, MonthEnd = 6 UNION SELECT Q … Continue reading →

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

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

Posted on February 24, 2015 by Prashanth Jayaram

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 – Read CSV files using OpenRowSet

Posted on February 9, 2015 by Prashanth Jayaram

We can directly access a CSV file using T-SQL. Input file Configure server to run Ad Hoc Distributed Queries sp_configure ‘Ad Hoc Distributed Queries’,1 GO reconfigure GO Check for Microsoft Access Driver on your system SQL select [hour], UserID, [ReportLaunch] … Continue reading →

Posted in SQL, Uncategorized | Tagged , , , , , , | 4 Comments