DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 98--------------------------------------------------
       File  Stream Information 
       By: srinivasma_exceldbp Date: November 10, 2014, 12:32 am
       ---------------------------------------------------------
       EXEC sp_configure filestream_access_level, 2
       RECONFIGURE
       GO
       -- Create Database
       CREATE DATABASE FileTableDB
       ON PRIMARY
       (Name = FileTableDB,
       FILENAME = 'D:\FileTable\FTDB.mdf'),
       FILEGROUP FTFG CONTAINS FILESTREAM
       (NAME = FileTableFS,
       FILENAME='D:\FileTable\FS')
       LOG ON
       (Name = FileTableDBLog,
       FILENAME = 'D:\FileTable\FTDBLog.ldf')
       WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
       DIRECTORY_NAME = N'FileTableDB');
       GO
       -- Check the Filestream Options
       SELECT DB_NAME(database_id),
       non_transacted_access,
       non_transacted_access_desc
       FROM sys.database_filestream_options;
       GO
       -- Create FileTable Table
       USE FileTableDB
       GO
       CREATE TABLE FileTableTb AS FileTable
       WITH
       (FileTable_Directory = 'FileTableTb_Dir');
       GO
       SELECT *
       FROM FileTableTb
       GO
       INSERT INTO [dbo].[FileTableTb]
       ([name],[file_stream])
       SELECT
       'CopyFileTable.txt', * FROM OPENROWSET(BULK
       N'd:\CopyFileTable.txt', SINGLE_BLOB) AS FileData
       GO
       SELECT *
       FROM FileTableTb
       GO
       -- Value set in Configuration Manager
       SELECT SERVERPROPERTY('FilestreamConfiguredLevel');
       -- Actual value
       SELECT SERVERPROPERTY('FilestreamEffectiveLevel');
       -- Get the share name
       SELECT SERVERPROPERTY('FilestreamShareName');
       /*
       To find which databases are using FILESTREAMING.
       Query sys.database.filestream_options to see which databases
       have
       filestream enabled.
       There’s information on the FILESTREAM filegroup in
       sys.filegroups.
       Even better is looking at sys.database_files
       */
       -- Find all FILESTREAM enabled databases
       SELECT d.Name AS Database_Name, dfo.non_transacted_access,
       dfo.non_transacted_access_desc, dfo.directory_name
       FROM sys.database_filestream_options AS dfo
       INNER JOIN sys.databases AS d
       ON dfo.database_id = d.database_id;
       -- Check  Filestream filegroup
       USE FileTableDB
       SELECT *
       FROM sys.filegroups
       WHERE type = 'FD';
       SELECT *
       FROM sys.database_files;
       /*
       To find which tables the FILESTREAM data is in,
       and if any of them are FileTables.
       */
       -- Find FILESTREAM tables
       SELECT * FROM sys.tables
       WHERE filestream_data_space_id IS NOT NULL;
       -- Find FILESTREAM columns
       SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName
       FROM sys.columns
       WHERE is_filestream = 1;
       -- Find FileTables
       SELECT * FROM sys.tables
       WHERE is_filetable = 1;
       /*
       The FileTableRootPath function returns
       the UNC path of the database share.
       If you use the FileTable name as a parameter
       it returns the path for that FileTable. And if you want
       the the full path for each file in the
       FileTable use the GetFileNamespacePath function.
       By default GetFileNamespacePath returns the relative path.
       If you want the full UNC path use 1 as the parameter.
       There’s also an optional parameter to convert
       the path to NetBios (the default), to use the fully qualified
       path,
       or not to convert the path at all.
       */
       --Full Root Path
       SELECT FileTableRootPath ('dbo.FileTableTb') AS SharePath
       -- Options for columnname.GetFileNamespacePath
       --  is_full_path - returns relative (0 default) or full UNC path
       (1)
       --  @option - converts path to NetBios (0 default), no
       conversion (1), or complete path (2)
       --Relative Path
       SELECT file_stream.GetFileNamespacePath() AS FileLocation
       FROM dbo.FileTableTb;
       --Full Path
       SELECT file_stream.GetFileNamespacePath(1) AS FileLocation
       FROM dbo.FileTableTb;
       -- Complete path
       SELECT file_stream.GetFileNamespacePath(1, 2) AS FileLocation
       FROM dbo.FileTableTb;
       *****************************************************