Please Share your Product Ideas with us!

When working on testing the 'Fetch' process in Collect, I found that Added by and Added on were not populated on the new system type group.

 

This request may have been made earlier.  I wasn't able to easily find the request.  Regardless, this request should be a high priority and implemented in the software development process so that records added to our systems are always properly documented.  The following three queries can be executed on any delivered application to test for compliance.

 

Find INSERT statements when AddedBy is not present (This isn't complete as AddedOn, AddedVia and boastatus should also always be populated properly.  There may also be some false positives based on the technique used.  This would, though, be a good starting point.)

 

SELECT     TOP (100) PERCENT sys.sysobjects.name, sys.syscomments.text, sys.sysobjects.xtype

FROM         sys.sysobjects INNER JOIN

                      sys.syscomments ON sys.sysobjects.id = sys.syscomments.id

WHERE     (sys.sysobjects.name LIKE N'%InsSel') AND (NOT (sys.syscomments.text LIKE N'%addedby%')) OR

                      (sys.sysobjects.name LIKE N'%ins') AND (NOT (sys.syscomments.text LIKE N'%addedby%')) OR

                      (sys.syscomments.text LIKE N'%Insert Into%' AND NOT (sys.syscomments.text LIKE N'%addedby%')) AND (sys.sysobjects.xtype = 'P')

ORDER BY sys.sysobjects.name, sys.sysobjects.xtype

 

 

This view shows all tables in a database that don't have any of the four tables populated.  If any views fail in this process, the required fields would be missing

 

SELECT     TOP (100)

                      PERCENT 'SELECT     ''' + sys.sysobjects.name + ''' AS TableName, MAX(AddedBy) AS MaxAddedBY, MAX(AddedOn) AS MaxAddedOn, MAX(AddedVia) AS MaxAddedVia, MAX(boaStatus) AS MaxBOAStatus,

                      COUNT(*) AS CountOfRecords

FROM         dbo.'

                       + sys.sysobjects.name + '

WHERE     ((AddedOn IS NULL) OR

                      (AddedVia IS NULL OR

                      AddedVia = N'''') OR

                      (boaStatus IS NULL) OR

                      (AddedBy IS NULL OR

                      AddedBy = N''''))

Having  count(*) <>0 '

                       AS [--SQL]

FROM         sys.sysobjects INNER JOIN

                      sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id

WHERE     (sys.sysobjects.xtype = 'U') AND (sys.syscolumns.name IN (N'AddedBy', N'AddedOn', N'AddedVia', N'boastatus'))

GROUP BY 'SELECT     ''' + sys.sysobjects.name + ''' AS TableName, MAX(AddedBy) AS MaxAddedBY, MAX(AddedOn) AS MaxAddedOn, MAX(AddedVia) AS MaxAddedVia, MAX(boaStatus) AS MaxBOAStatus,

                      COUNT(*) AS CountOfRecords

FROM         dbo.'

                       + sys.sysobjects.name + '

WHERE     ((AddedOn IS NULL) OR

                      (AddedVia IS NULL OR

                      AddedVia = N'''') OR

                      (boaStatus IS NULL) OR

                      (AddedBy IS NULL OR

                      AddedBy = N''''))

Having  count(*) <>0 '

HAVING      (COUNT(*) = 4)

 

This view shows all tables in a delivered database which does not contain all four of the tested fields.  It is possible that all four fields are not always required, but that requirement can be refined once this is implemented.

 

SELECT     TOP (100) PERCENT sys.sysobjects.name, COUNT(*) AS CountOfFields

FROM         sys.sysobjects INNER JOIN

                      sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id

WHERE     (sys.sysobjects.xtype = 'U') AND (sys.syscolumns.name IN (N'AddedBy', N'AddedOn', N'AddedVia', N'boastatus'))

GROUP BY sys.sysobjects.name

HAVING      (COUNT(*) <> 4)