text: ﻿CREATE PROCEDURE dbo.aspnet_PersonalizationAdministration_FindState (
      @AllUsersScope bit,
      @ApplicationName NVARCHAR(256),
      @PageIndex              INT,
      @PageSize               INT,
      @Path NVARCHAR(256) = NULL,
      @UserName NVARCHAR(256) = NULL,
      @InactiveSinceDate DATETIME = NULL)
  AS
  BEGIN
      DECLARE @ApplicationId UNIQUEIDENTIFIER
      EXEC dbo.aspnet_Personalization_GetApplicationId @ApplicationName, @ApplicationId OUTPUT
      IF (@ApplicationId IS NULL)
          RETURN
  
      -- Set the page bounds
      DECLARE @PageLowerBound INT
      DECLARE @PageUpperBound INT
      DECLARE @TotalRecords   INT
      SET @PageLowerBound = @PageSize * @PageIndex
      SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
  
      -- Create a temp table to store the selected results
      CREATE TABLE #PageIndex (
          IndexId int IDENTITY (0, 1) NOT NULL,
          ItemId UNIQUEIDENTIFIER
      )
  
      IF (@AllUsersScope = 1)
      BEGIN
          -- Insert into our temp table
          INSERT INTO #PageIndex (ItemId)
          SELECT Paths.PathId
          FROM dbo.aspnet_Paths Paths,
               ((SELECT Paths.PathId
                 FROM dbo.aspnet_PersonalizationAllUsers AllUsers, dbo.aspnet_Paths Paths
                 WHERE Paths.ApplicationId = @ApplicationId
                        AND AllUsers.PathId = Paths.PathId
                        AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                ) AS SharedDataPerPath
                FULL OUTER JOIN
                (SELECT DISTINCT Paths.PathId
                 FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Paths Paths
                 WHERE Paths.ApplicationId = @ApplicationId
                        AND PerUser.PathId = Paths.PathId
                        AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                ) AS UserDataPerPath
                ON SharedDataPerPath.PathId = UserDataPerPath.PathId
               )
          WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
          ORDER BY Paths.Path ASC
  
          SELECT @TotalRecords = @@ROWCOUNT
  
          SELECT Paths.Path,
                 SharedDataPerPath.LastUpdatedDate,
                 SharedDataPerPath.SharedDataLength,
                 UserDataPerPath.UserDataLength,
                 UserDataPerPath.UserCount
          FROM dbo.aspnet_Paths Paths,
               ((SELECT PageIndex.ItemId AS PathId,
                        AllUsers.LastUpdatedDate AS LastUpdatedDate,
                        DATALENGTH(AllUsers.PageSettings) AS SharedDataLength
                 FROM dbo.aspnet_PersonalizationAllUsers AllUsers, #PageIndex PageIndex
                 WHERE AllUsers.PathId = PageIndex.ItemId
                       AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
                ) AS SharedDataPerPath
                FULL OUTER JOIN
                (SELECT PageIndex.ItemId AS PathId,
                        SUM(DATALENGTH(PerUser.PageSettings)) AS UserDataLength,
                        COUNT(*) AS UserCount
                 FROM aspnet_PersonalizationPerUser PerUser, #PageIndex PageIndex
                 WHERE PerUser.PathId = PageIndex.ItemId
                       AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
                 GROUP BY PageIndex.ItemId
                ) AS UserDataPerPath
                ON SharedDataPerPath.PathId = UserDataPerPath.PathId
               )
          WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
          ORDER BY Paths.Path ASC
      END
      ELSE
      BEGIN
          -- Insert into our temp table
          INSERT INTO #PageIndex (ItemId)
          SELECT PerUser.Id
          FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths
          WHERE Paths.ApplicationId = @ApplicationId
                AND PerUser.UserId = Users.UserId
                AND PerUser.PathId = Paths.PathId
                AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path))
                AND (@UserName IS NULL OR Users.LoweredUserName LIKE LOWER(@UserName))
                AND (@InactiveSinceDate IS NULL OR Users.LastActivityDate <= @InactiveSinceDate)
          ORDER BY Paths.Path ASC, Users.UserName ASC
  
          SELECT @TotalRecords = @@ROWCOUNT
  
          SELECT Paths.Path, PerUser.LastUpdatedDate, DATALENGTH(PerUser.PageSettings), Users.UserName, Users.LastActivityDate
          FROM dbo.aspnet_PersonalizationPerUser PerUser, dbo.aspnet_Users Users, dbo.aspnet_Paths Paths, #PageIndex PageIndex
          WHERE PerUser.Id = PageIndex.ItemId
                AND PerUser.UserId = Users.UserId
                AND PerUser.PathId = Paths.PathId
                AND PageIndex.IndexId >= @PageLowerBound AND PageIndex.IndexId <= @PageUpperBound
          ORDER BY Paths.Path ASC, Users.UserName ASC
      END
  
      RETURN @TotalRecords
  END
  GO
  GRANT EXECUTE
      ON OBJECT::[dbo].[aspnet_PersonalizationAdministration_FindState] TO [aspnet_Personalization_ReportingAccess]
      AS [dbo];
  
  