Getting information about your LMS courses from SQL - TSR

The following is a SQL script you can run on the server that will provide you with the CourseID, Course Title, Scorm File Name, LMS Test ID, and Number of Sessions for each course in LMS. This information can be used to see if you have courses without tests, courses without sessions to sign up for, or courses that have LMS tests to review.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT Courses.[CourseID]
      ,[Title]
      ,[ScormFileName]
      ,[ScormFileActualName]
      ,[TestID]
      ,numSessions
  FROM [Courses] LEFT JOIN (SELECT [CourseID]
      ,count([CourseID]) as numSessions
  FROM [Sessions]
  group by CourseID) S on S.courseid = Courses.CourseID
  where isarchived = 0
  --Course doesn't have SCORM
  --and DATALENGTH([ScormFileActualName]) < 1 
  --Course doesn't have LMS Test
  --and TestID is null
  --Course has no sessions
  --and numSessions is null

This will give you:
1. The CourseID and Title for the course. Mostly informational but will let you find it quickly using search.
2. The ScormFileName - The name of the zip file uploaded to the course.
3. The ScormFileActualName - This is the folder created on the server that the SCORM course gets unzipped to. This is the more important of the two fields; if there's a value here, there's a SCORM associated with the course.
4. TestID - The ID of the LMS Test associated with the course. If it is NULL, there is none. If there is a number, that is the test ID. It should not be possible to have both a TestID and a ScormFileActualName.
5. numSessions - The number of Sessions for the course. If it is NULL, then there are no sessions.

This query returns only non-deleted courses. I have added 6 commented lines, in pairs, underneath the query that will further refine the query if you don't want to do it in an outside program like Excel. The Course line tells you the description. Keep this one commented. Uncomment the line starting with "and" to amend the query to filter items to meet the "Course" line above it.