http://www.sqlservercentral.com/blogs/jeffrey_yao/2011/08/14/counting-code-lines-in-sps-with-powershell/

Printed 2014/07/28 04:30PM

Counting code lines in SPs with Powershell

2011/08/14

One of interesting tasks I once had is to check the # of code lines for all user stored procedures (SPs) in a user database.

I once developed a pure yet lengthy t-sql solution by counting the new line ASCII code (i.e. char(0×13) + char(0×10) ).

However, with PowerShell and SMO, it is actually much easier to do so and here is a script to count # of lines and total words for all user SPs in my local SQL Server 2K8 instance (for AdventureWorks database)

$srv = New-Object (“Microsoft.SQLServer.Management.SMO.Server”) “<MyComputer>”
$db = $srv.databases[‘AdventureWorks’]
$db.StoredProcedures | ? { ! $_.IsSystemObject } | % {$_.TextBody} | measure –line –word

image

Of course you need to load SMO DLL file first, which I put into my Powershell profile, as the following

[System.Reflection.Assembly]::LoadFrom(“C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll”) | Out-Null



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.