SQL Server – Where are your files and what names have you given them

Background

Sometimes the question is simple.

What folders are your data and log files sitting on.

And, how have you named them.

Code

SQL


; with cteFileName
(
          [database]
        , physicalName
        , physicalNameInReverse
        , [type]
        , [typeLiteral]
        , [filenameLength]

)
as
(

    select
            [database]
                = tblSD.[name]

          , physicalName
                = tblSMF.[physical_name]

          , physicalNameInReverse
              =  reverse
                 (
                    tblSMF.[physical_name]
                 )

          , [type]
                = tblSMF.[type]

          , [typeLiteral]
                = tblSMF.[type_desc]

        , [filenameLength]
                = len(tblSMF.[physical_name])

    from  sys.databases tblSD

    inner join sys.master_files tblSMF

            on tblSD.database_id = tblSMF.database_id

)
, cteFileNamePosition
(
          [database]
        , physicalName
        , physicalNameInReverse
        , [type]
        , [typeLiteral]
        , [filenameLength]
        , [positionofExtension]
        , [positionofBackSlashOnFileName]
)
as
(
    select
          [database]
        , physicalName
        , physicalNameInReverse
        , [type]
        , [typeLiteral]
        , [filenameLength]
        , [positionofExtension]
            = case

                when
                    charindex
                    (
                        '.'
                        , cteFN.physicalNameInReverse
                    ) = 0 then null

                when (
                        [type] in
                        (
                              0 -- ROWS
                            , 1 -- LOG

                        )
                        and
                        (
                            charindex
                            (
                                '.'
                                , cteFN.physicalNameInReverse
                            ) > 0
                        )
                    )

                    then 

                        [filenameLength]
                        -
                        (
                            charindex
                            (
                                '.'
                                , cteFN.physicalNameInReverse
                            )
                            - 1
                        )

              end                        

        , [positionofBackslashOnFileName]
            = case

                when
                    charindex
                    (
                        '\'
                        , cteFN.physicalNameInReverse
                    ) = 0 then null

                when (
                        [type] in
                        (
                              0 -- ROWS
                            , 1 -- LOG

                        )
                        and
                        (
                            charindex
                            (
                                '\'
                                , cteFN.physicalNameInReverse
                            ) > 0
                        )
                    )

                    then 

                    (

                        [filenameLength]
                        -
                        (
                            charindex
                            (
                                '\'
                                , cteFN.physicalNameInReverse
                            )
                        )

                    )

              end           

    from cteFileName cteFN 

)
select
          [database]

       -- ,  cteFN.[type]

        ,  cteFN.[typeLiteral]

        ,  cteFN.physicalName 

       -- ,  cteFN.physicalNameInReverse 

       -- , [cteFN].positionofExtension

        , [folder]
            = case

                when
                    (cteFN.[type]=2)
                        then cteFN.physicalName 

                when ( cteFN.positionofBackSlashOnFileName >0)
                       then substring
                            (
                                  cteFN.physicalName
                                , 1
                                , cteFN.positionofBackSlashOnFileName + 1
                             )

                end      

        , [filename]
            = case

                when
                    (cteFN.[type]=2)
                        then null

                when ( cteFN.positionofBackSlashOnFileName >0)
                       then substring
                            (
                                  cteFN.physicalName
                                , cteFN.positionofBackSlashOnFileName + 2
                                , 255
                             )

                end                                   

        , [ext]
            = case

                when
                    (cteFN.[type]=2)
                        then null

                when ( cteFN.positionofExtension >0)
                       then substring
                            (
                                  cteFN.physicalName
                                , cteFN.positionofExtension + 1
                                , cteFN.filenameLength
                                    - cteFN.positionofExtension
                             )

                end                         

from   cteFileNamePosition cteFN

Output

database.datafiles.SkySync.01.20190724.0354PM

Source Code Control

GitHub

DanielAdeniji/SQLServerDatabaseFiles
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s