Printed 2017/01/20 09:28PM

SQLTreeo – Migration of extended properties to local storage

By Jakub Dvorak, 2013/03/28

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:

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: , , , , ,

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.