iVend Database Database : GETDATABASELIST Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@USERNAME

In

VarWChar

100

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[GETDATABASELIST]

(

@USERNAME AS NVARCHAR(100) = NULL

)

AS

SET NOCOUNT ON;

DECLARE @LOGINNAME SYSNAME;

DECLARE @DBNAME SYSNAME;

DECLARE @VARSQL NVARCHAR(4000);

DECLARE @DBSQL NVARCHAR(4000);

DECLARE @ENTERPRISENAME NVARCHAR(100);

DECLARE @PARMDEFINITION NVARCHAR(500);

Declare @TenantName NVARCHAR(500);

IF (OBJECT_ID('TEMPDB..#TEMP_DATABASEINFO') IS NOT NULL)

BEGIN

          DROP TABLE #TEMP_DATABASEINFO

END

IF (OBJECT_ID('TEMPDB..#TEMP_IVENDDATABASES') IS NOT NULL)

BEGIN

          DROP TABLE #TEMP_IVENDDATABASES

END

CREATE TABLE #TEMP_DATABASEINFO

  (

   LOGINNAME                       SYSNAME  COLLATE DATABASE_DEFAULT NOT NULL

  ,DBNAME                          SYSNAME  COLLATE DATABASE_DEFAULT NOT NULL

  ,USERNAME                        SYSNAME  COLLATE DATABASE_DEFAULT NOT NULL

  ,USERORALIAS                     CHAR(8)  COLLATE DATABASE_DEFAULT NOT NULL

  )

CREATE TABLE #TEMP_IVENDDATABASES

  (

DBNAME                          SYSNAME    COLLATE DATABASE_DEFAULT NOT NULL

  ,MCVERSION                       NVARCHAR(100)  COLLATE DATABASE_DEFAULT NOT NULL

  ,DBVERSION                       NVARCHAR(100)  COLLATE DATABASE_DEFAULT NOT NULL

  ,ENTERPRISENAME                 NVARCHAR(100)  COLLATE DATABASE_DEFAULT NOT NULL

  ,TENANTNAME     NVARCHAR(100)  COLLATE DATABASE_DEFAULT NOT NULL

  )

  IF(@USERNAME IS NOT NULL)

  BEGIN

  INSERT INTO #TEMP_DATABASEINFO EXEC SP_IVENDHELPLOGINS @USERNAME;

  END

  ELSE

  BEGIN

SET @LOGINNAME = SUSER_NAME();

----COMMENTS : INSERT THE ACCESSIBLE DATABASE FOR THE WINDOWS USER

INSERT INTO #TEMP_DATABASEINFO EXEC SP_IVENDHELPLOGINS @LOGINNAME;

END

DELETE FROM #TEMP_DATABASEINFO WHERE DBNAME IN ('MASTER','MODEL','MSDB','TEMPDB')

-----------------------------------------------------------------START CURSOR----------------------------------------------------------

DECLARE USERDBINFO CURSOR LOCAL FOR SELECT DISTINCT DBNAME FROM #TEMP_DATABASEINFO

OPEN USERDBINFO -- OPEN THE CURSOR

FETCH NEXT FROM USERDBINFO INTO @DBNAME

WHILE @@FETCH_STATUS = 0

BEGIN

IF ((OBJECT_ID(@DBNAME + N'.DBO.CFGVERSION', N'U') IS NOT NULL) AND (OBJECT_ID(@DBNAME + N'.DBO.CfgTenantInformation', N'U') IS NOT NULL))

BEGIN

  SET @ENTERPRISENAME = ''

  SET @VARSQL = 'USE [' + @DBNAME + ']; SELECT @ENTERPRISENAMEOUT = COMPANYNAME FROM CFGENTERPRISE'

  SET @PARMDEFINITION = N'@ENTERPRISENAMEOUT NVARCHAR(100) OUTPUT';

  EXECUTE SP_EXECUTESQL @VARSQL, @PARMDEFINITION, @ENTERPRISENAMEOUT=@ENTERPRISENAME OUTPUT;

  Set @TenantName = ''''

  IF(@ENTERPRISENAME IS NULL OR @ENTERPRISENAME = '')

  BEGIN

    SET @ENTERPRISENAME = @DBNAME

  END

  --PRINT '@DBNAME ' + @DBNAME

  SET @DBSQL = 'USE [' + @DBNAME + ']; INSERT INTO #TEMP_IVENDDATABASES SELECT '''+ @DBNAME + ''' AS DBNAME, MCVERSION,DBVERSION, '''+ REPLACE(@ENTERPRISENAME,'''','''''') + ''' AS ENTERPRISENAME, IsNull(CfgTenantInformation.VendorString,'''')  FROM CFGVERSION LEFT OUTER JOIN CfgTenantInformation ON 1=1 '

  --Select  @DBSQL

  EXEC SP_EXECUTESQL @DBSQL

  END

FETCH NEXT FROM USERDBINFO INTO @DBNAME

END

CLOSE USERDBINFO -- CLOSE THE CURSOR

DEALLOCATE USERDBINFO -- DEALLOCATE THE CURSOR

--------------------------------------------------------------------END CURSOR----------------------------------------------------------

SELECT * FROM #TEMP_IVENDDATABASES