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 – 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

1 comments, 81 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, 179 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, 233 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, 123 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, 167 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, 53 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, 205 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, 41 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, 39 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, 274 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, 287 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, 50 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, 38 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, 126 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, 152 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, 36 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, 30 reads

Posted in PowerSQL By Prashanth Jayaram on 2 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, 230 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, 363 reads

Posted in PowerSQL By Prashanth Jayaram on 17 March 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, 52 reads

Posted in PowerSQL By Prashanth Jayaram on 17 March 2014

Older posts