Database extension

ObjectGears system can be easily extended also on the database level. This extension is supported by several stored procedures and functions.

Use the functions Get_...RoleCode instead of hardcoding of the name in your code to avoid future compatibility issues.

Use of function for mapping role ID (role ID can be different in each ObjectGears instance).

select r.id, r.code
from [role] r
where r.Code = dbo.Get_AuthenticateUsersRoleCode()

select r.id, r.code
from [role] r
where r.Code = dbo.Get_ReportRoleCode()

Functions FindClassDef and FindColumnDbName are used to get class ID and database name of columns. These names are necessary for dynamic creating SQL on the database level in order it can be portable to other instances.

ALTER PROCEDURE [dbo].[AddStatistics]
AS
BEGIN
 SET NOCOUNT ON;

 declare @m_code varchar(100)
 set @m_code = 'systemutil'

 Declare @date varchar(200)
 select @date = 'DateAdd(day, ' + cast( datepart( day, getdate()) as varchar(10)) + ' - 1, DateAdd(month, ' + cast( datepart( month, getdate()) as varchar(10)) + ' - 1, DateAdd(Year, ' + cast( datepart( year, getdate()) as varchar(10)) + ' - 1900, DateAdd(Hour, ' + cast( datepart( hour, getdate()) as varchar(10)) + ', DateAdd(Minute, ' + cast( datepart( minute, getdate()) as varchar(10)) + ', 0)))))'

 declare @clName varchar(50)
 declare @col_rowsName varchar(50)
 declare @col_total_space varchar(50)
 declare @col_total_used varchar(50)
 declare @sql varchar(max)
 declare @cl_id int

 --nacist cl
 set @cl_id = dbo.FindClassDef( @m_code, 'stat_system')
 set @col_rowsName = dbo.FindColumnDbName( @cl_id, 'rows')
 set @col_total_space = dbo.FindColumnDbName( @cl_id, 'total_space_kb')
 set @col_total_used = dbo.FindColumnDbName( @cl_id, 'total_used_space_kb')
 set @clName = 'DataRow' + cast( @cl_id as varchar(10))

 --spocitat pocet zaznamu
 set @sql = 'insert into ' + @clName + '(created, CreatedDataSourceId, Creator, ShortDescription, ' + @col_rowsName + ', ' + @col_total_space + ', ' + @col_total_used + ')
     select ' + @date + ', 4, ''SYSTEM'', t.name, p.rows, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB
      from sys.tables t
       INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
       INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
       INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
       LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
      where t.type_desc = ''USER_TABLE''
       and t.name not in ( ''appversion'')
       and t.name not like ''datarow%''
       and t.name not like ''aspnet_%''
       and (t.name not like ''wp%'' or t.name = ''wpbase'')
       and s.Name = ''dbo''
      GROUP BY t.Name, s.Name, p.Rows'
 exec ( @sql)
 
END

You can create a View or  a Stored procedure also in case when finding information by means of ObjectGears scripts would be labour intensive, demanding on computing or lead to a forced use of the functionality. You can build ObjectGears Query over the View or Stored procedure and then filter, sort and use the query where you need.