if (not exists (select * from sys.filegroups where name ='FStream'))
begin
declare @FilePathAndName nvarchar(4000)= (select physical_name FROM sys.master_files where database_id=db_ID() and file_id=1)
set @FilePathAndName= REPLACE(@FilePathAndName,'.mdf','_FileStream.fs')
--print @FilePathAndName
declare @sqlStr as nvarchar(max) ='alter database ' + db_name() +' ADD FILEGROUP FStream CONTAINS FILESTREAM'+CHAR(13)
set @sqlStr = @sqlStr + 'ALTER DATABASE ' + db_name() +' ADD FILE ( NAME = N''FStream'', FILENAME = N'''+@FilePathAndName +''' ) TO FILEGROUP fStream'+CHAR(13)
set @sqlStr = @sqlStr + 'ALTER DATABASE ' + db_name() +' SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'''+db_name()+'FilesDir'')'+CHAR(13)
set @sqlStr = @sqlStr + 'CREATE TABLE dbo.MyFiles AS FILETABLE WITH ( FILETABLE_DIRECTORY = ''MyFileDir'')'+CHAR(13)
print @sqlStr
exec (@SqlStr)
end
No comments:
Post a Comment