Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

SQL – Find encrypted objects using sql_modules or Powershell

sys.sql_modules catalog view which got introduced from SQL 2005 onwards. The sql_modules which includes the objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module.

 SQL

SELECT 
sm.object_id, 
OBJECT_NAME(sm.object_id) AS object_name, 
o.type, o.type_desc, 
sm.definition, 
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0

Read more

0 comments, 62 reads

Posted in PowerSQL By Prashanth Jayaram on 14 April 2014

First Birthday to My Blog

Today is the First year anniversary of my blog. After thinking a lot and going through many un-imaginable resource available over the internet and reading blogs of many wonderful brains made me think about creating a blog.

When I wrote my first post a year ago I wondered and asked… Read more

0 comments, 66 reads

Posted in PowerSQL By Prashanth Jayaram on 10 April 2014

PowerShell GUI Tool – Network Ping – Version 2

The first version of the tool was developed by Amandhally and it can be found in the below path. Thanks for sharing.

http://www.amandhally.net/2012/08/08/powershell-based-network-range-pinging-application-network-pinger-ajit-2/

In my version of the tool, I’ve re-used his template and modified in such a way that the output is HTML formatted with server Names displayed in… Read more

0 comments, 124 reads

Posted in PowerSQL By Prashanth Jayaram on 2 April 2014

PowerShell – System Information

Different ways to find system information are given below:-

  • Method 1:Querying WMI objects
    • Download the code SystemInfo
    • Create the function with Powershell console and call the function with ComputerName as its argument
.SYNOPSIS 
Get Complete details of any server Local or remote 
.DESCRIPTION 
This function uses WMI class to…

Read more

0 comments, 199 reads

Posted in PowerSQL By Prashanth Jayaram on 17 March 2014

SQL Jobs – Complete Information – SQL 2000/2005/2008/R2

SQL Job details – The script can be executed across all SQL version from SQL 2000 onwards.

The below are the columns listed in the output

  1. ServerName
  2. ScheduleID
  3. ScheduleName
  4. IsEnabled
  5. ScheduleType
  6. Occurrence
  7. Recurrence
  8. Frequency
  9. ScheduleUsage
  10. StartDate
  11. ScheduleUsage
  12. EndDate
  13. ScheduleCreatedOn
  14. ScheduleLastModifiedOn

For more information about the columns refer the below the…

Read more

0 comments, 113 reads

Posted in PowerSQL By Prashanth Jayaram on 11 March 2014

SQL – Backup Report

The simple query to find the database backup status

The select statment consists of

  1. ServerName
  2. dbname
  3. BackupStartDate
  4. BackupFinishDate
  5. BackupAge
  6. Size
  7. status
  8. Type

The query will only run from sql 2005 and later version.

Select  
 SERVERPROPERTY('ServerName'), 
 db.name, 
 CONVERT(VARCHAR(10), b.backup_start_date, 103) +   + convert(VARCHAR(8), b.backup_start_date, 14) backup_start_date, 
 CONVERT(VARCHAR(10), b.backup_finish_date, 103) +   +…

Read more

2 comments, 128 reads

Posted in PowerSQL By Prashanth Jayaram on 5 March 2014

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 server hardware or software changes are taking place, it’s…

Read more

1 comments, 183 reads

Posted in PowerSQL By Prashanth Jayaram on 3 March 2014

PowerShell – SSRS Inventory – Automatic – Excel File – EMAIL

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.

Download the file and save as SSRSInventory.PS1.

It has five mandatory parameters

  1. InputFileName – Text File contains a list of SQL Servers  -C:\Server.txt(Example)
  2. DirectoryToSave – Folder…

Read more

0 comments, 200 reads

Posted in PowerSQL By Prashanth Jayaram on 27 February 2014

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 a SQL server Instance using DSN and then SQL output…

Read more

3 comments, 2,219 reads

Posted in PowerSQL By Prashanth Jayaram on 24 February 2014

PowerShellGUI Tools – Disk,Memory and Process – Modules

I’ve created a script which gives details of Disk, Memory and Process and its represented graphically.

Here, the scripts are enclosed in module. At its simplest, a module is just a PowerShell script, contained in a file with a .PSM1 extension

A module is some chunk of code that you… Read more

0 comments, 731 reads

Posted in PowerSQL By Prashanth Jayaram on 20 February 2014

Top 10 Process Listing By Memory Consumption – GUI Tool

This Powershell GUI tool is used to list Top 10 memory consumption process of a given computer[local /Remote]. In the screen that follows, enter the computer name that you would like to get the top 10 processes and hit Get Process button.

This pulls out the memory consumed by top… Read more

0 comments, 138 reads

Posted in PowerSQL By Prashanth Jayaram on 7 February 2014

PowerShell – SQL Inventory – Automatic – Excel File – EMAIL

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.

Download the file and save as SQLInventoryExcel.PS1.

It has Five mandatory parameters

  1. InputFileName – Text File contains a list of SQL Servers -c:\Server.txt(Example)
  2. DirectoryToSave – Folder…

Read more

5 comments, 286 reads

Posted in PowerSQL By Prashanth Jayaram on 4 February 2014

PowerShell – Memory Details GUI Tool

This Powershell Memory GUI tool is used to fetch memory information from a computer. In the screen that follows, enter the computer name that you would like to get the memory details for and click on Get Memory Button.
This pulls out the usage of memory of the listed computer.… Read more

0 comments, 1,322 reads

Posted in PowerSQL By Prashanth Jayaram on 27 January 2014

PowerShell – Program List – 32/64 Bit – Local/Remote Machies

This Powershell script list all the installed application on both 32 and 64 bit applications, particularly useful for people managing both 32-bit and 64-bit applications.   This code also contains an exclusion array where you can exclude list of program that you don’t want to show.

  • The function allows -ComputerName parameter…

Read more

4 comments, 156 reads

Posted in PowerSQL By Prashanth Jayaram on 20 January 2014

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…

Read more

0 comments, 72 reads

Posted in PowerSQL By Prashanth Jayaram on 13 January 2014

T-SQL – Monitoring DiskSpace of Multiple Servers

This post talks about step by step approach to capture the disk space using T-SQL. This is a request from one of SQL enthusiast. The requirement is to do with T-SQL to monitor disk space of remote servers.

Pre-requisites are

  1. Enable XP_CMDShell 
  2. Enable Ole automation on all servers

Step by… Read more

0 comments, 140 reads

Posted in PowerSQL By Prashanth Jayaram on 10 January 2014

SSRS – Identify a scheduled SSRS Report

Initiating subscription is a tedious task in SQL Server Reporting Services. Each subscription in Reporting Services is setup as a SQL Server Agent job, but the job names are Unique Identifier its hard to remember and re-call. Have complete information in single query makes one easy to validate and execute… Read more

0 comments, 109 reads

Posted in PowerSQL By Prashanth Jayaram on 10 January 2014

Powershell – Age of File Report – Local or Remote

Here’s a very quick post by request.I just had a request for searching the file system for files matching certain criteria such as file age based on the date created. The function Get-FileAgingReport uses get-childitem cmdlet. This function uses two mandatory input parameters Computername and Folder Path.The function is validated… Read more

0 comments, 224 reads

Posted in PowerSQL By Prashanth Jayaram on 8 January 2014

PowerShell – Find OS architecture (32 bit or 64 bit) of local or remote machines Using Powershell

Method 1: Environment Variable

PS:\>$ENV:PROCESSOR_ARCHITECTURE

Method 2: Using Win32_OperatingSystem

ps:\>$computername=’abcd’

ps:\>(Get-WmiObject Win32_OperatingSystem -computername $computername).OSArchitecture

 Method 3: Using Win32_Processor

ps:\>$computername=’abcd’

ps:\>Get-WmiObject -Class Win32_Processor -ComputerName $ComputerName| Select-Object AddressWidth

Method 4:[IntPtr]::Size - Gets the size of this instance.  The value of this property is 4 in a 32-bit process, and 8 in a… Read more

0 comments, 237 reads

Posted in PowerSQL By Prashanth Jayaram on 6 January 2014

Older posts