(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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; *****************************************************