Last weekend I happened to be in one of the beautiful cities in Sri Lanka for some fun time with SQL Enthusiasts. We had our first ever SQLSaturday Event in Sri Lanka and it was just too much fun to be amongst the amazing audiences. I was fortunate to deliver a couple of sessions and not to forget the hours and hours of interaction with the attendees post session. In this blog post we will answer the question How Many Line of Code Do You Have in Database?
SQLSaturdays and Usergroups are an amazing way to learn and share your experience with our SQL lovers from the neighboring country. I couldn’t have asked for a better reception and hospitality. I am eagerly looking forward for the future SQLSaturdays too.
In one of those late night SQL talks, one of my friends asked an interesting question. They were a product based company and one of their clients was asking how many total lines of code they have in their product. My instinct was to say – “Doesn’t Visual Studio help you find this in a jiffy? Getting this data must have been some right click somewhere?” My friend looked at me and said – “Pinal, I never had a problem with my .NET code because VS did help him in a lot of ways. His problem was when working with SQL Server.” Trust me, I am no VS expert here – but the SQL part of the question did get me thinking. I said – “I think I can come up with something may be a couple of days after returning to India”.
I thought of putting together something rudimentary and basic to get the ball rolling. So here is my first shot at this requirement.
SELECT
DB_NAME(DB_ID()) [DB_Name],
TYPE,
COUNT(*) AS Object_Count,
SUM(LinesOfCode) AS LinesOfCode
FROM (
SELECT
TYPE,
LEN(definition)- LEN(REPLACE(definition,CHAR(10),'')) AS LinesOfCode,
OBJECT_NAME(OBJECT_ID) AS NameOfObject
FROM sys.all_sql_modules a
JOIN sysobjects s
ON a.OBJECT_ID = s.id
-- AND xtype IN('TR', 'P', 'FN', 'IF', 'TF', 'V')
WHERE OBJECTPROPERTY(OBJECT_ID,'IsMSShipped') = 0
) SubQuery
GROUP BY TYPE
The above script gives a high level numbers of the LOC inside SQL Server by counting the number of “Carriage return” keys in our various functions, modules, procedures, triggers etc. I know it may not be perfect or scientific. But this is my first shot at this requirement. I ran the same against the AdventureWorks database to get the below output.
I would love to know any other methods you have ever used in your environments for this requirement? Do you need such numbers in your projects? What have you done for such requirements? Let me know via your comments so that we can learn together.
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
