Studio – Super Field Logic for Multiple Database or Company Access

​Datamodel Super Field logic is allows reports over multiple Databases or Companies and also consolidation.   The Super Field is created using Studio and in this example it is setup to scan a Database Server for muliple instances of a specific Database Schema and display then as a possible list for reporting over in Sharperlight.

SQL Statement to get all Databases where the Table EmployeeAppraisal exists

DECLARE @staging-sharperlight-com.stackstaging.comnext_pgDb nvarchar(50)
DECLARE @staging-sharperlight-com.stackstaging.comRawDblist nvarchar(MAX)
DECLARE @staging-sharperlight-com.stackstaging.comDblist nvarchar(MAX) =‘<DEFAULT>’ –valid list of db codes, always have Default
DECLARE @staging-sharperlight-com.stackstaging.comDbSql nvarchar(MAX) =”
DECLARE @staging-sharperlight-com.stackstaging.comDbDefaultSql nvarchar(MAX) =”
DECLARE @staging-sharperlight-com.stackstaging.comDbCount int =0
DECLARE @staging-sharperlight-com.stackstaging.comDbAccessList nvarchar(MAX) = LTrim(Rtrim(replace(‘{_Product.Property.DbAccessList}’ ,‘ ‘,”)))
DECLARE @staging-sharperlight-com.stackstaging.comsql nvarchar(MAX)
DECLARE @staging-sharperlight-com.stackstaging.comcode nvarchar(MAX)
DECLARE @staging-sharperlight-com.stackstaging.comdescription nvarchar(MAX)
DECLARE @staging-sharperlight-com.stackstaging.comExitsCheck int=0
DECLARE md_pgDb_CURSOR CURSOR
FOR
SELECT name FROM master..sysdatabases where dbid > 4
and (
–Global Setting to restrict access to subset, if blank then all dbs
CHARINDEX( ‘,’+ Replace(UPPER(name),‘ ‘,”)+ ‘,’ , ‘,’ + UPPER(@staging-sharperlight-com.stackstaging.comDbAccessList) + ‘,’)> 0
OR len(@staging-sharperlight-com.stackstaging.comDbAccessList)=0
)

FOR READ ONLY
OPEN md_pgDb_CURSOR FETCH NEXT FROM md_pgDb_CURSOR INTO @staging-sharperlight-com.stackstaging.comnext_pgDb
WHILE @staging-sharperlight-com.stackstaging.com@staging-sharperlight-com.stackstaging.comFETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @staging-sharperlight-com.stackstaging.comsql = ‘select @staging-sharperlight-com.stackstaging.comexitsCheck=count(*) from ‘ + @staging-sharperlight-com.stackstaging.comnext_pgDb + ‘.dbo.EmployeeAppraisal’
execute sp_executesql @staging-sharperlight-com.stackstaging.comsql,N’@staging-sharperlight-com.stackstaging.comexitsCheck int OUTPUT’,@staging-sharperlight-com.stackstaging.comexitsCheck OUTPUT
–print ‘Valid PG Db ‘+ @staging-sharperlight-com.stackstaging.comnext_pgDb
–Get Database code, default to db name if property no present
SET @staging-sharperlight-com.stackstaging.comsql= ‘use ‘ + @staging-sharperlight-com.stackstaging.comnext_pgDb + ‘ select @staging-sharperlight-com.stackstaging.comcode=ISNULL((SELECT cast(value as nvarchar) from sys.extended_properties WHERE class=0 and name = ”Code”),Replace(DB_NAME(),” ”,””))’
execute sp_executesql @staging-sharperlight-com.stackstaging.comsql,N’@staging-sharperlight-com.stackstaging.comcode nvarchar(max) OUTPUT’ ,@staging-sharperlight-com.stackstaging.comcode OUTPUT
–Get Database description
SET @staging-sharperlight-com.stackstaging.comsql= ‘use ‘ + @staging-sharperlight-com.stackstaging.comnext_pgDb + ‘ select @staging-sharperlight-com.stackstaging.comdescription=ISNULL((SELECT cast(value as nvarchar) from sys.extended_properties WHERE class=0 and name = ”Description”),””)’
execute sp_executesql @staging-sharperlight-com.stackstaging.comsql,N’@staging-sharperlight-com.stackstaging.comdescription nvarchar(max) OUTPUT’ ,@staging-sharperlight-com.stackstaging.comdescription OUTPUT
print ‘Valid PG Db ‘+ @staging-sharperlight-com.stackstaging.comnext_pgDb + ‘ Code:’ + @staging-sharperlight-com.stackstaging.comcode + ‘ Desc: ‘ + @staging-sharperlight-com.stackstaging.comdescription

–Make virtual table of results
SET @staging-sharperlight-com.stackstaging.comDbSql = @staging-sharperlight-com.stackstaging.comDbSql + ‘ union select ”’ +@staging-sharperlight-com.stackstaging.comcode + ”’ as Code ,”’+ @staging-sharperlight-com.stackstaging.comdescription + ”’ as Description ,”’ + @staging-sharperlight-com.stackstaging.comnext_pgDb +”’ as DbName’ + CHAR(13)
SET @staging-sharperlight-com.stackstaging.comDblist = @staging-sharperlight-com.stackstaging.comDblist + ‘,’ +@staging-sharperlight-com.stackstaging.comcode
SET @staging-sharperlight-com.stackstaging.comDbCount = @staging-sharperlight-com.stackstaging.comDbCount +1
IF UPPER(@staging-sharperlight-com.stackstaging.comnext_pgDb) = UPPER(DB_NAME())
BEGIN
–Set Default Company details
SET @staging-sharperlight-com.stackstaging.comDbDefaultSql = ‘select ”<DEFAULT>” as Code,”’+ @staging-sharperlight-com.stackstaging.comdescription + ”’ as Description ,”’ + @staging-sharperlight-com.stackstaging.comnext_pgDb +”’ as DbName’ + CHAR(13)
END
END TRY
BEGIN CATCH
–print ‘Not PG db ‘ + @staging-sharperlight-com.stackstaging.comnext_pgDb
END CATCH
FETCH NEXT FROM md_pgDb_CURSOR
INTO @staging-sharperlight-com.stackstaging.comnext_pgDb

END
IF @staging-sharperlight-com.stackstaging.comDbCount=1 SET @staging-sharperlight-com.stackstaging.comDbSql=” –Only One Database so just use Default
CLOSE md_pgDb_CURSOR
DEALLOCATE md_pgDb_CURSOR
–Create State Values in Sharperlight memory
select ‘DbSql’,@staging-sharperlight-com.stackstaging.comDbDefaultSql + @staging-sharperlight-com.stackstaging.comDbSql
union
select ‘DbValidList’,@staging-sharperlight-com.stackstaging.comDblist