System Generated Index Names are Causing syntax issues

seanmcne commented 5 years ago

I am working in a DB where some system generated index names (below) are causing syntax issues. I realize the syntax is totally incorrect, but since this is coming from a product system generating the indexes, I think the script here can probably be changed to accommodate odd naming

The constraint name I'm running up against is named 'EPK[dbo].[testprofile2_role]' (you can't make this up, I suspect it was initially a bug on creating the default constraints).

I'm thinking to escape it correctly the query to get the index names can be altered slightly to add a "replace(idxs.name, ']',']]')" - such as:

select 
    i.[object_id]
    ,ObjectSchema = OBJECT_SCHEMA_NAME(i.object_id)
    ,ObjectName = object_name(i.object_id) 
    ,IndexName = replace(idxs.name, ']',']]')
    ,i.avg_fragmentation_in_percent
    ,i.page_count
    ,i.index_id
    ,i.partition_number
    ,i.index_type_desc
    ,i.avg_page_space_used_in_percent
    ,i.record_count
    ,i.ghost_record_count
    ,i.forwarded_record_count
    ,null as OnlineOpIsNotSupported

seanmcne commented 5 years ago

Feel free to take a look - here's the update I used in our test environment and it worked well. It looks like the proper escape sequence is to double up the closing bracket to escape it out

Before: EPK[dbo].[testprofile2_systemuser] After: [EPK[dbo]].[testprofile2_systemuser]]]

https://github.com/yochananrachamim/AzureSQL/compare/master...seanmcne:master

vytux-com commented 5 years ago

Wouldn't you rename the broken index names once and then the database would be fixed up and no longer an issue?

yochananrachamim commented 5 years ago

if this can happen to index name, it might be the case with statistics name, table or schema name as well. I'm not sure if we should make the change to this global script or maybe it will be better for you to change the code in your environment as this is extremely edge case, any other thoughts about it?

yochananrachamim commented 3 years ago

I was able to fix it very easily, please download the latest version that contain the fix to enjoy this and more cool features.

yochananrachamim / AzureSQL

System Generated Index Names are Causing syntax issues #4