Shine.mm If this copied correctly
(select filename from sys.sysfiles where fileid =1 and filename not like ''H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf''),
You have a EXTRA ' between like and and the drive letter H and after the _Data.mdf Modifiying your code to eliminate those additional characters
(select filename from sys.sysfiles where fileid =1
and filename not like ('H:\MSSQL\'+@database_name+'\DATA\'+@database_name+'_Data.mdf'))
does indeed generate a return.
sys.files filename location is stored as case sensitive,
I have tested the following 2 select statements and they show that the filename entry in sys.sysfiles is NOT case sensitive.
SELECT fileid, name
FROM sys.sysfiles
WHERE filename NOT LIKE ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TIPSnTRICKS_Data.MDF')
SELECT fileid, name
FROM sys.sysfiles
WHERE filename NOT LIKE ('c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tipsntricks_data.mdf')