Counting code lines in SPs with Powershell

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(0x13) + char(0x10) ).

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

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s