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

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