Fetch Record count from all the Tables in SQL Database

The below script will provide list of record in each table of the SQL Database.

SELECT DISTINCT t.name AS TableName,
i.rows AS RecordCnt 
FROM sysindexes i
INNER JOIN sys.tables t
ON i.id = t.OBJECT_ID 
WHERE t.is_ms_shipped = 0 
ORDER BY t.name

Useful Windows Powershell Script

Some of the useful powershell script which can be used in our day to day life.

1. Get running scheduled tasks on a Windows system.

(get-scheduledtask).where({$_.state -eq \’running\’})

2. Get system uptime from multiple computers

Get-CimInstance Win32_operatingsystem -ComputerName $computers |
Select-Object PSComputername,LastBootUpTime,
@{Name=\”Uptime\”;Expression = {(Get-Date) – $_.LastBootUptime}}

3. Get drive utilization using PSDrives

Get-PSDrive -PSProvider filesystem | where-object {$_.used -gt 0} |
select-Object -property Root,@{name=\”SizeGB\”;expression={($_.used+$_.free)/1GB -as [int]}},
@{name=\”UsedGB\”;expression={($_.used/1GB) -as [int]}},
@{name=\”FreeGB\”;expression={($_.free/1GB) -as [int]}},
@{name=\”PctFree\”;expression={[math]::round(($_.free/($_.used+$_.free))*100,2)}}

4. List of Installed applications

Get-PSDrive -PSProvider filesystem | where-object {$_.used -gt 0} |
select-Object -property Root,@{name=\”SizeGB\”;expression={($_.used+$_.free)/1GB -as [int]}},
@{name=\”UsedGB\”;expression={($_.used/1GB) -as [int]}},
@{name=\”FreeGB\”;expression={($_.free/1GB) -as [int]}},
@{name=\”PctFree\”;expression={[math]::round(($_.free/($_.used+$_.free))*100,2)}}

5. Get details about all external scripts in your %PATH%.

gcm -commandtype externalscript | Get-Item |
Select-Object Directory,Name,Length,CreationTime,LastwriteTime,
@{name=\”Signature\”;Expression={(Get-AuthenticodeSignature $_.fullname).Status }}

6. Get event log utilization for remote computers defined in $computers

gcim Win32_NTEventLogFile -computer $computers -filter \”NumberOfRecords > 0\” |
Select-Object @{Name=\”Computername\”;Expression={$_.CSName}},
LogFileName,
NumberOfRecords,
@{Name=\”Size(KB)\”;Expression={$_.FileSize/1kb}},
@{Name=\”MaxSize(KB)\”;Expression={($_.MaxFileSize/1KB) -as [int]}},
@{name=\”PercentUsed\”;Expression={[math]::round(($_.filesize/$_.maxFileSize)*100,2)}} |
Sort Computername,PercentUsed |
Format-Table -GroupBy Computername -property LogFileName,NumberOfRecords,*Size*,PercentUsed

7. Get freespace for drive C on the local computer formatted in GB

(gcim win32_logicaldisk -filter \”deviceid = \’C:\’\”).FreeSpace/1gb

#or use the PSDrive

(gdr c).Free/1gb

8. Get a date string in the format year-month-day-hour-min-second

get-date -format yyyyMMddhhmmss

9. Get the last time your computer booted

(gcim win32_operatingsystem).LastBootUpTime

#or modify to get uptime

(get-date) – ((gcim win32_operatingsystem).LastBootUpTime)

10. Get configured TrustedHosts.

(get-wsmaninstance wsman/config/client).trustedhosts

11. Get all drives identified by a standard drive letter.

get-volume -driveletter (97..122) -ErrorAction SilentlyContinue

12. Get total physical memory formatted as GB

gcim win32_computersystem -computer SRV1,SRV2 | Select PSComputername,@{N=\”Memory\”;E={$_.TotalPhys
icalMemory/1GB -as [int]}}

13. Get IPv4 addresses on your local adapters.

Get-NetIPAddress -AddressFamily IPv4 | where-object IPAddress -notmatch \”^(169)|(127)\” | Sort-Object IPAddress | select IPaddress,Interface*

14. Find all processes that use a given module (dll).

get-process | Where { $_.Modules.filename -match \”netapi32.dll\”}

15. List all PowerShell profile script settings

$profile | select *host* | fl

16. Get the current date time as UTC time

(get-date).ToUniversalTime()
#or pretty it up
\”$((get-date).ToUniversalTime()) UTC\”

\”$((get-date).ToUniversalTime().tolongdatestring()) UTC\”

17. Get a formatted report of all commands with synopsis.

(Get-Command).where( { $_.source }) | Sort-Object Source, CommandType, Name |
Format-Table -GroupBy Source -Property CommandType, Name, @{Name = \”Synopsis\”; Expression = { (Get-Help $_.name).Synopsis}}

18. Unlock-ADAccount

Parameter Set: Default
Unlock-ADAccount [-Identity] [-AuthType {Negotiate | Basic} ] [-Credential ,PSCredential]

Unlock-ADAccount -identity

19. Set-ADAccountPassword

$SecureString=ConvertTo-SecureString -string Abcd1234 -AsPlainText -Force

Set-ADAccountPassword -Identity TestAccount -Reset -NewPassword $SecureString

 

20. Remotely restart or shut down another computer

Start-Sleep 60; Restart-Computer –Force –ComputerName TARGETMACHINE

Stop-Computer –computer DC1 –Credential ashish\\administrator