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.

Capturing DB Usage stats – sp_spaceused simulation using DMV’s.

SP_SPACEUSED

sp_spaceused is one of the system stored procedure used frequently by many dba’s that reveals amount of space used by database/tables. This procedure can be executed either by passing parameters or no parameters. The objective of this stored proc is to measure the amount of space consumed by database… Read more

2 comments, 5,063 reads

Posted in PowerSQL By Prashanth Jayaram on 24 November 2014

Determine size of the file using T-SQL/Powershell/Ole Objects

There are many ways to find the filesize.

This post is all about identifing the filesize using T-SQL,Instantiating OLE Objects and PowerShell. The requirement is to retrieve the size of the specified file.

The return value is going to a floating point value. The details and script are given below Read more

0 comments, 176 reads

Posted in PowerSQL By Prashanth Jayaram on 7 November 2014

PowerShell- Monitor Group Of Services on ‘N’ Servers & Notify Stopped Services

This post explains how to monitor a multiple services on a list of servers and send alert when the given service is in stopped state. This script will allow you to read a list of servers from the input file and establish connection to all remote servers to pull service… Read more

3 comments, 137 reads

Posted in PowerSQL By Prashanth Jayaram on 6 November 2014

T-SQL – Scripting Stored Procedure to Create backup copy on same database

This post is one of the request from OP.

Download SQL  Backup SP in the same database

The probelm statement 1:-

Create a backup copy of a group of stored procedures on the same database.  Using TSQL I need to find all stored procedures that meet a criteria (WHERE pr.name… Read more

0 comments, 182 reads

Posted in PowerSQL By Prashanth Jayaram on 14 October 2014

SQL – List Primary key and Concatenate composite Keys into a single row separed by comma

The following query lists all the Primary keys of all the table. 

OP has requested to list all PK and composite PKs and concatenate all it’s composite PK column values into a single row separated by comma hence this post.

 
SELECT TAB.TABLE_NAME,COL.COLUMN_NAME  
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TAB,  
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE COL  
WHERE  
    COL.CONSTRAINT_NAME =…

Read more

3 comments, 6,539 reads

Posted in PowerSQL By Prashanth Jayaram on 10 September 2014

USING SQL & POWERSHELL – Different Methods to List Databases WITH Recovery Models

USING POWERSHELL TO LIST SQLSERVER USER DATABASES WITH RECOVERY MODELS

One of op requested to list all the user defined databases where it has been configured with simple recovery model across ‘N’ servers.There are many ways to achieve the requirement. 

You can query sys.databases with sqlcmd utility to list all… Read more

0 comments, 353 reads

Posted in PowerSQL By Prashanth Jayaram on 15 August 2014

PowerShell- Monitoring Group Of Services on Group of Servers with Alternate Credential

This post explains how to monitor a multiple services on a group of servers with alternate credentials.

This script will allow you to read a list of servers from the input file and use credentials to connect remote/DMZ servers to pull service status information for a set of specific services… Read more

1 comments, 286 reads

Posted in PowerSQL By Prashanth Jayaram on 3 July 2014

PowerShell- Monitoring Group Of Services on Group of Servers with Alternate Credential

This post explains how to monitor a multiple services on a group of servers with alternate credentials.

This script will allow you to read a list of servers from the input file and use credentials to connect remote/DMZ servers to pull service status information for a set of specific services… Read more

1 comments, 167 reads

Posted in PowerSQL By Prashanth Jayaram on 3 July 2014

PowerShell – Perfmon Counters into CSV File – Multiple remote servers

Introduction

This script can be used for exporting specified performance counter values from multiple remote target servers to CSV file. The challenge is to gather the specific counter information and append it to CSV file with Powershell2.0.

Performance counters

The script will pull the below mentioned counter values

  • Processor (_total)\%…

Read more

3 comments, 240 reads

Posted in PowerSQL By Prashanth Jayaram on 10 June 2014

PowerShell – Perfmon Counters into CSV File – Multiple remote servers

Introduction

This script can be used for exporting specified performance counter values from multiple remote target servers to CSV file. The challenge is to gather the specific counter information and append it to CSV file with Powershell2.0.

Performance counters

The script will pull the below mentioned counter values

  • Processor (_total)\%…

Read more

3 comments, 96 reads

Posted in PowerSQL By Prashanth Jayaram on 10 June 2014

PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

This script is used to discover all the server information and captures disk drive utilization details. The drive details are aggregated based on the status of free space. The aggregated data is pictorially represented using pie chart. The excel sheet is then send to intended users.

The script requires input… Read more

0 comments, 261 reads

Posted in PowerSQL By Prashanth Jayaram on 21 May 2014

PowerShell – OS Inventory and Disk Info – Consolidated Excel File – EMAIL

This script is used to discover all the server information and captures disk drive utilization details. The drive details are aggregated based on the status of free space. The aggregated data is pictorially represented using pie chart. The excel sheet is then send to intended users.

The script requires input… Read more

0 comments, 71 reads

Posted in PowerSQL By Prashanth Jayaram on 21 May 2014

SQL – List All Tables, Space Usage Info & Other Details

One of the OP requested in the forum to find all table details of any database. The details which includes Number of Rows,table size, index size etc:-. Even I frequently writing queries to list all such details.

I’ve used SQLCMD mode to connect to the remote server and database to… Read more

0 comments, 63 reads

Posted in PowerSQL By Prashanth Jayaram on 1 May 2014

SQL – List All Tables, Space Usage Info & Other Details

One of the OP requested in the forum to find all table details of any database. The details which includes Number of Rows,table size, index size etc:-. Even I frequently writing queries to list all such details.

I’ve used SQLCMD mode to connect to the remote server and database to… Read more

0 comments, 332 reads

Posted in PowerSQL By Prashanth Jayaram on 1 May 2014

PowerShell-Input-Validation Scripts

Input values are validated using various mechanisms.  let’s start by examining how you would validate input parameters in Windows PowerShell 2.0

The Inputs are validated for the below mentioned functionality

  1. PostalCode
  2. PhoneNumber
  3. EmailID
  4. IP 
  5. FileName

Download the scripts from the below link

http://gallery.technet.microsoft.com/PowerShell-Input-Validation-30ebaa51

To validate PostalCode – 99999

Function Validate-PostalCode…

Read more

0 comments, 446 reads

Posted in PowerSQL By Prashanth Jayaram on 28 April 2014

PowerShell-Input-Validation Scripts

Input values are validated using various mechanisms.  let’s start by examining how you would validate input parameters in Windows PowerShell 2.0

The Inputs are validated for the below mentioned functionality

  1. PostalCode
  2. PhoneNumber
  3. EmailID
  4. IP 
  5. FileName

Download the scripts from the below link

http://gallery.technet.microsoft.com/PowerShell-Input-Validation-30ebaa51

To validate PostalCode – 99999

Function Validate-PostalCode…

Read more

0 comments, 88 reads

Posted in PowerSQL By Prashanth Jayaram on 28 April 2014

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, 89 reads

Posted in PowerSQL By Prashanth Jayaram on 14 April 2014

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, 193 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, 176 reads

Posted in PowerSQL By Prashanth Jayaram on 10 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, 59 reads

Posted in PowerSQL By Prashanth Jayaram on 10 April 2014

Older posts