any tool that obfuscates an entire database schema?

Question

any tool that obfuscates an entire database schema?

Sheldon Hull

Right there with Babe

Points: 757
More actions
May 6, 2016 at 9:21 am

#318713

You know the fantastic SQL Sentry Plan Explorer Pro has an anonymizer feature that lets you share an execution plan with anonymized table/column names. I was thinking that perhaps there is a tool out there that does the same thing for an entire database, allowing one to get support/help from vendors that request a copy of your database schema to troubleshoot.
I don't need data obfuscated, as I know there are ways to do that, but I would like to know if there is anything out there (free is good 🙂 ) that does just an empty schema so basically I could send a red gate sql compare snapshot of a obfuscated schema if needed.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 1

obfuscate object names? that might make things difficult to get help with, because someone might suggest a change to the schema of a table, you have the mapping issue; but As long as you can reverse it back, you'd be ok, so you need to be consistent.?

the obvious issue is that after we've renamed objects and columns, but objects that references those items, like procs/views/functions, would become invalid.

I seem to remember that SQL Prompt has a rename function in the GUI? can someone confirm that? does it go through dependencies to help with renaming?

my craptastic query i started investigating with, until i realized the dependencies will kill me.

SELECT 'EXEC sp_rename ''' + name collate SQL_Latin1_General_CP1_CI_AS + ''',''' + type_desc collate SQL_Latin1_General_CP1_CI_AS + convert(varchar,object_id) + ''';'

from sys.objects

select 'EXEC sp_rename ''' + quotename(tabz.name) collate SQL_Latin1_General_CP1_CI_AS + '.' + quotename(colz.name) collate SQL_Latin1_General_CP1_CI_AS + ''',''' + 'col_' + convert(varchar,colz.column_id) + ''',''COLUMN'';'

from sys.columns colz

inner join sys.tables tabz

on colz.object_id = tabz.object_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sheldon Hull Right there with Babe Points: 757 More actions · Answer 2

thanks! Exactly what I'm wonder. With SQL Sentry Plan explorer they anonymize to "column1", but all the column references in plan are consistent. So I was thinking something would take the schema and just abstract actual names to generic names, but they'd still keep everything consistent in FK.

Haven't found anything yet 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

SqlBarbarian (5/6/2016)
thanks! Exactly what I'm wonder. With SQL Sentry Plan explorer they anonymize to "column1", but all the column references in plan are consistent. So I was thinking something would take the schema and just abstract actual names to generic names, but they'd still keep everything consistent in FK.
Haven't found anything yet 🙂

well within the Plan, it's really just a find and replace in the XML; it's a self contained cosmos, so it's good target for that, but the overall schema, it's a bit bigger, a lot harder to obfuscate, as we are seeing now.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

SqlBarbarian (5/6/2016)
You know the fantastic SQL Sentry Plan Explorer Pro has an anonymizer feature that lets you share an execution plan with anonymized table/column names. I was thinking that perhaps there is a tool out there that does the same thing for an entire database, allowing one to get support/help from vendors that request a copy of your database schema to troubleshoot.
I don't need data obfuscated, as I know there are ways to do that, but I would like to know if there is anything out there (free is good 🙂 ) that does just an empty schema so basically I could send a red gate sql compare snapshot of a obfuscated schema if needed.

Just let me develop on it for a while, it'll soon be hard as **** to read! :w00t:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);