ÿþSET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[DatabaseSelect] (@DatabaseList nvarchar(max))



RETURNS @Database TABLE (DatabaseName nvarchar(max) NOT NULL)



AS



BEGIN



  ----------------------------------------------------------------------------------------------------

  --// Source: http://ola.hallengren.com                                                          //--

  ----------------------------------------------------------------------------------------------------



  DECLARE @DatabaseItem nvarchar(max)

  DECLARE @Position int



  DECLARE @CurrentID int

  DECLARE @CurrentDatabaseName nvarchar(max)

  DECLARE @CurrentDatabaseStatus bit



  DECLARE @Database01 TABLE (DatabaseName nvarchar(max))



  DECLARE @Database02 TABLE (ID int IDENTITY PRIMARY KEY,

                             DatabaseName nvarchar(max),

                             DatabaseStatus bit,

                             Completed bit)



  DECLARE @Database03 TABLE (DatabaseName nvarchar(max),

                             DatabaseStatus bit)



  DECLARE @Sysdatabases TABLE (DatabaseName nvarchar(max))



  ----------------------------------------------------------------------------------------------------

  --// Split input string into elements                                                           //--

  ----------------------------------------------------------------------------------------------------



  WHILE CHARINDEX(', ',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,', ',',')

  WHILE CHARINDEX(' ,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,' ,',',')

  WHILE CHARINDEX(',,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,',,',',')



  IF RIGHT(@DatabaseList,1) = ',' SET @DatabaseList = LEFT(@DatabaseList,LEN(@DatabaseList) - 1)

  IF LEFT(@DatabaseList,1) = ','  SET @DatabaseList = RIGHT(@DatabaseList,LEN(@DatabaseList) - 1)



  SET @DatabaseList = LTRIM(RTRIM(@DatabaseList))



  WHILE LEN(@DatabaseList) > 0

  BEGIN

    SET @Position = CHARINDEX(',', @DatabaseList)

    IF @Position = 0

    BEGIN

      SET @DatabaseItem = @DatabaseList

      SET @DatabaseList = ''

    END

    ELSE

    BEGIN

      SET @DatabaseItem = LEFT(@DatabaseList, @Position - 1)

      SET @DatabaseList = RIGHT(@DatabaseList, LEN(@DatabaseList) - @Position)

    END

    IF @DatabaseItem <> '-' INSERT INTO @Database01 (DatabaseName) VALUES(@DatabaseItem)

  END



  ----------------------------------------------------------------------------------------------------

  --// Handle database exclusions                                                                 //--

  ----------------------------------------------------------------------------------------------------



  INSERT INTO @Database02 (DatabaseName, DatabaseStatus, Completed)

  SELECT DISTINCT DatabaseName = CASE WHEN DatabaseName LIKE '-%' THEN RIGHT(DatabaseName,LEN(DatabaseName) - 1) ELSE DatabaseName END,

                  DatabaseStatus = CASE WHEN DatabaseName LIKE '-%' THEN 0 ELSE 1 END,

                  0 AS Completed

  FROM @Database01



  ----------------------------------------------------------------------------------------------------

  --// Resolve elements                                                                           //--

  ----------------------------------------------------------------------------------------------------



  WHILE EXISTS (SELECT * FROM @Database02 WHERE Completed = 0)

  BEGIN



    SELECT TOP 1 @CurrentID = ID,

                 @CurrentDatabaseName = DatabaseName,

                 @CurrentDatabaseStatus = DatabaseStatus

    FROM @Database02

    WHERE Completed = 0

    ORDER BY ID ASC



    IF @CurrentDatabaseName = 'SYSTEM_DATABASES'

    BEGIN

      INSERT INTO @Database03 (DatabaseName, DatabaseStatus)

      SELECT [name], @CurrentDatabaseStatus

      FROM sys.databases

      WHERE [name] IN('master','model','msdb','tempdb')

    END

    ELSE IF @CurrentDatabaseName = 'USER_DATABASES'

    BEGIN

      INSERT INTO @Database03 (DatabaseName, DatabaseStatus)

      SELECT [name], @CurrentDatabaseStatus

      FROM sys.databases

      WHERE [name] NOT IN('master','model','msdb','tempdb')

    END

    ELSE IF @CurrentDatabaseName = 'ALL_DATABASES'

    BEGIN

      INSERT INTO @Database03 (DatabaseName, DatabaseStatus)

      SELECT [name], @CurrentDatabaseStatus

      FROM sys.databases

    END

    ELSE IF CHARINDEX('%',@CurrentDatabaseName) > 0

    BEGIN

      INSERT INTO @Database03 (DatabaseName, DatabaseStatus)

      SELECT [name], @CurrentDatabaseStatus

      FROM sys.databases

      WHERE [name] LIKE REPLACE(PARSENAME(@CurrentDatabaseName,1),'_','[_]')

    END

    ELSE

    BEGIN

      INSERT INTO @Database03 (DatabaseName, DatabaseStatus)

      SELECT [name], @CurrentDatabaseStatus

      FROM sys.databases

      WHERE [name] = PARSENAME(@CurrentDatabaseName,1)

    END



    UPDATE @Database02

    SET Completed = 1

    WHERE ID = @CurrentID



    SET @CurrentID = NULL

    SET @CurrentDatabaseName = NULL

    SET @CurrentDatabaseStatus = NULL



  END



  ----------------------------------------------------------------------------------------------------

  --// Handle tempdb and database snapshots                                                       //--

  ----------------------------------------------------------------------------------------------------



  INSERT INTO @Sysdatabases (DatabaseName)

  SELECT [name]

  FROM sys.databases

  WHERE [name] <> 'tempdb'

  AND source_database_id IS NULL



  ----------------------------------------------------------------------------------------------------

  --// Return results                                                                             //--

  ----------------------------------------------------------------------------------------------------



  INSERT INTO @Database (DatabaseName)

  SELECT DatabaseName

  FROM @Sysdatabases

  INTERSECT

  SELECT DatabaseName

  FROM @Database03

  WHERE DatabaseStatus = 1

  EXCEPT

  SELECT DatabaseName

  FROM @Database03

  WHERE DatabaseStatus = 0



  RETURN



  ----------------------------------------------------------------------------------------------------



END

GO