What is the easiest way to get total number for lines of code (LOC) in SQL Server?

Question

I need to provide statistics on how many lines of code (LOC) associated with a system. The application part is easy but I need to also include any code residing within the SQL Server database. This would apply to stored procedures, functions, triggers, etc.

How can I easily get that info? Can it be done (accurately) with TSQL by querying the system tables\sprocs, etc?

Answer 1

Personally you might just script the objects to file using SQL Server Management tools, it will get a few extras in there for the checks to do the drop first incase the object exists.

Answer 2

Just select all the text from syscomments and count how many lines you have. The text column is text, which you can't really see in Management studio, so I would write a program or power shell script like this:

$conn = new-object System.Data.SqlClient.SqlConnection("Server=server;Database=database;Integrated Security=SSPI")
$cmd = new-object System.Data.SqlClient.SqlCommand("select text from syscomments", $conn)
$conn.Open()
$reader = $cmd.ExecuteReader()

$reader.Read() | out-null
$reader.GetString(0) | clip
$reader.Close()
$conn.Close()

Paste into an editor that has a line count, and you're done.

Answer 3

In Management Studio, right click the database you want a line count for... select Tasks -> Generate Scripts, you can select script options in the Scripts Wizard to include or exclude objects, when you have it set the way you like it can generate to a new query window