PowerShell and SQL – Easy and Different Ways to Find Number of Rows of all Tables in a database

SQL Example:

DECLARE @TableRowsCount table
(
Tablename varchar(50),
cnt int)

insert into @TableRowsCount
EXEC sp_MSforeachtable @command1=” select ‘?’,count(*) from ?”

select * from @TableRowsCount

PowerShell Example:

Load SQL PowerShell by typing “SQLPS” in PowerShell console

PS:\>SQLPS

Change Directory to the desired database

PS SQLSERVER:\ cd SQL\HQDB001\default\databases\DummyDBName\tables

PS SQLSERVER:\SQL\HQDB001\default\databases\Powersql\tables> dir |format-table  Name,Rowcount

SQL 2005 or above – Find Transaction Rate on a busy OLTP Database

Change Database name of a below mentioned query

DECLARE @cntr_value1 bigint
DECLARE @cntr_value2 bigint

SELECT ‘BEFORE’

SELECT @cntr_value1 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘transactions/sec’
AND object_name = ‘SQLServer:Databases’
AND instance_name =’ABCD‘ /*DB NAME*/

select ‘ABCD’ DBNAME, @cntr_value1 ‘Lower Limit’

WAITFOR DELAY ’00:00:30′

SELECT ‘AFTER’

SELECT @cntr_value2 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘transactions/sec’
AND object_name = ‘SQLServer:Databases’
AND instance_name =’ABCD’ /*DB NAME*/

select ‘ABCD’ DBNAME , @cntr_value2 ‘Upper Limit’

Select @cntr_value2 – @cntr_value1 ‘Transactions per 30 secs’

PowerShell – CHECK, START and STOP SQL Services of a remote servers

Replace a valid server name in the following examples. Try to do a testing on your local machine and understand before you execute it on any Production Server.

Example 1: Find all SQL related services on HQSQ001

Get-Service -ComputerName HQSQ001 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

Example 2: Find all SQL related services on multiple servers[Separate the server name by comma]

Get-Service -ComputerName HQSQ001,HQSQ002 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

Example 3: Stop only SQL REPORTING Services on both the server

Get-Service -ComputerName HQSQ001,HQSQ002 | ?{$_.Displayname -match “SQL SERVER REPORT”}|%{$_.stop()}

Example 4: Start only SQL REPORTING Services on both the server

Get-Service -ComputerName HQSQ001,HQSQ002 | ?{$_.Displayname -match “SQL SERVER REPORT”}|%{$_.start()}

 

Find SQL Server Installation – Date and Time – 2005 Onwards

SELECT create_date as ‘SQL Server Install Date’ FROM sys.server_principals WHERE name=’NT AUTHORITY\SYSTEM’

PowerShell – Pattern Search – Local or Remote Folder

#Description : List all the files of a local or remote drive, run this PowerShell script using PowerShell-ISE.exe for better execution and output.

Steps:- Copy the content and  paste it in Get-AllFilesSearchbyPattern.PS1  and Execute.

#Input Parameter List – Server Name, Drive Name, Extension and Pattern. If you want to search in specific Folder you need to type ‘Yes‘ if not type ‘No‘

Or You can create only the function and call the function with parameters. which is given below

For Example –

1. Get-FileBySearchingPattern  HQDB001 D  SQL TLOG_PowerShell No

It will traverse through all files of D Drive on HQDB001 Server for TLOG_PowerShell as it’s searching string.

2. Get-FileBySearchingPattern  HQDB001 D  SQL TLOG_PowerShell Yes

Enter the folder that you want to do a search

PowerShell\PowerShell1

Now, path for the search becomes \\HQDB001\d$\PowerShell\PowerShell1\.

##########################

#Part 1 – Copy the below Content Get-AllFilesSearchbyPattern.PS1

##########################

Function Get-FileBySearchingPattern
{
Param([String]$server,[char]$drive,[String]$extn,[String]$pattern,[String]$Folder)
IF ($Folder -eq ‘No’)
{
Get-ChildItem \\$server\$drive$ *.$extn -recurse -Force -ErrorAction SilentlyContinue | ? {$_.psiscontainer -eq $false} | ? {gc $_.pspath |select-string -pattern “$pattern”}|select name,DirectoryName
}
elseif($folder -eq ‘Yes’)
{
$FolderStruncture = Read-Host ” Enter the Folder Name”
if(test-path \\$server\$drive$\$FolderStruncture -pathtype container)
{
Get-ChildItem \\$server\$drive$\$FolderStruncture *.$extn -recurse -Force -ErrorAction SilentlyContinue | ? {$_.psiscontainer -eq $false} | ? {gc $_.pspath |select-string -pattern “$pattern”}|select name,DirectoryName
}
else
{
write-host “Invalid directory Structure”
}
}
else
{
Write-host “Enter the correct option”
}

}

$server=Read-Host ” Enter the servername”
$drive = Read-Host ” Enter the DriveName”
$extn = Read-Host ” Enter the extn”
$pattern=Read-Host ” Enter the pattern to search”
$SearchFolder = Read-Host ” Do you need to Search the pattern in Folder (No/Yes)”

Get-FileBySearchingPattern $server $drive $extn $pattern $SearchFolder

*****************************

Part 2: Create function Get-FileBySearchingPattern and call the function by calling parameters.

*********************

Function Get-FileBySearchingPattern
{
Param([String]$server,[char]$drive,[String]$extn,[String]$pattern,[String]$Folder)
IF ($Folder -eq ‘No’)
{
Get-ChildItem \\$server\$drive$ *.$extn -recurse -Force -ErrorAction SilentlyContinue | ? {$_.psiscontainer -eq $false} | ? {gc $_.pspath |select-string -pattern “$pattern”}|select name,DirectoryName
}
elseif($folder -eq ‘Yes’)
{
$FolderStruncture = Read-Host ” Enter the Folder Name”
if(test-path \\$server\$drive$\$FolderStruncture -pathtype container)
{
Get-ChildItem \\$server\$drive$\$FolderStruncture *.$extn -recurse -Force -ErrorAction SilentlyContinue | ? {$_.psiscontainer -eq $false} | ? {gc $_.pspath |select-string -pattern “$pattern”}|select name,DirectoryName
}
else
{
write-host “Invalid directory Structure”
}
}
else
{
Write-host “Enter the correct option”
}

}

A man is a hero…

A man is a hero not because he is braver than anyone else; he is hero because he is brave for 10 minutes longer