New SQLTreeo version introduced local storage option when your folders are stored to file system as structure of files and folders. This option was added because many users had issues with storing their folders in extended properties of SQL objects (mostly because of permission issues).
Users who need to swap to local file system, migration of extended properties to local storage is necessary.
Here is one approach how you can do it:
- Export your current folders from extended properties to CSV file
- Run batch file which creates local storage based on CSV file
Exporting your extended properties in CSV
Use following script to export extended properties from all databases on your server. It dynamically executes SELECT statement against system tables and inserts results in #allfiles temporary table. All records are retrieved at the end of the scripts and must be exported as CSV directly from SSMS result grid. Save this CSV file for next step (as e.g. folders.csv). Important – keep your file in 1250/1252 encoding not in UTF-8.
DECLARE @sqlOneDb VARCHAR(1024)
DECLARE @sql VARCHAR(1024)
IF (OBJECT_ID('tempdb..#allfiles') IS NOT NULL)
DROP TABLE #allfiles
CREATE TABLE #allfiles (foldername VARCHAR(512), filename VARCHAR(512))
--select all objects which have extended property Virtual_Folder (are in some folder) (? sign will be replaced by database name)
SET @sqlOneDb = 'INSERT INTO #allfiles
SELECT DISTINCT
''Databases/?/'' +
CASE
WHEN type = ''U'' THEN ''Tables''
WHEN type = ''V'' THEN ''Views''
WHEN type = ''P'' THEN ''StoredProcedures''
WHEN type = ''FN'' THEN ''ScalarValuedFunctions''
WHEN type IN (''TF'',''IF'') THEN ''TableValuedFunctions''
END + ''/'' + CONVERT (varchar(255), p.value) as ''folder'',
SCHEMA_NAME(o.schema_id) + ''.'' + o.Name + ''.sqltreeo'' as ''file''
FROM sys.objects o
JOIN sys.extended_properties p ON p.major_id = o.object_id
WHERE type in (''U'',''V'',''P'',''IF'',''TF'',''FN'')
AND p.Name = ''VirtualFolder''
UNION ALL
SELECT
''Databases Virtual Folders/'' + CONVERT(VARCHAR(255), p.Value),
''?.sqltreeo''
FROM sys.extended_properties p
WHERE class_desc = ''DATABASE'' AND name = ''VirtualFolder'' '
SET @sql = 'USE [?]; ' + @sqlOneDb
-- run select for all databases on the server
EXECUTE master.sys.sp_MSforeachdb @sql
-- table is filled with all objects which belongs to any fodler
-- in format <database_name>/<folder_path>/<file_name>.sqltreeo
SELECT * FROM #allfiles
Running batch file which creates local storage
Simply save following loop in e.g. import.bat. Please read carefully instructions below script.
for /f "tokens=1,2 delims=;" %%a in (folders.csv) do (
md "%1\%2\%%a"
echo. 2>"%1\%2\%%a\%%b"
)
Loop in batch takes all records from “folders.csv” (in same path as your batch file), creates folders and empty files which represents SQLTreeo local storage.
%1 is first batch parameter and represents your desired root for local storage such as “C:\SQLTreeoStorage”
%2 is second batch parameter and represents server name for which you’re creating local storage (which you’ve exported extended properties from) (e.g. “DBSERVER”). In case of local connection use your computer name.
Usage of import.bat is following:
import.bat "c:\SQLTreeoStorage" "DBServer"
Now your local storage should be created at entered path (“C:\SQLTreeoStorage”). You can go to SSMS menu Tools->SQLTreeo and set this path as root for local storage. After SSMS restart you should see your folders.
Tags: Add-In, database grouping, folders, sql objects, SSMS, treeo
