How can we help you today? How can we help you today?

Aliases enhancement

If you haven't already considered this, please add an option to import a list of table aliases.

I've used an extended property TableAlias for every user table in my DB - the prospect of hand keying 100+ aliases (albeit only once) is a little offputting.

Text file, XML, query import, anything would be be well received...

Thanks for making such a huge improvement to this tool.
PDinCA
0

Comments

2 comments

Sort by
  • Lionel
    Hi thanks for the feedback. I will have a look at writing a simple tool that lets you import aliases and snippets but if I don't have time it is quite easy to do yourself. If you look at the user profile directory under Local Settings\Application Data\Red Gate\SQL Prompt 3 there is an EngineOptions.xml file which contains all of the aliases. It is a simple xml file and should be easy to change.

    Lionel
    Lionel
    0
  • PDinCA
    Just what I was hoping for... Thanks!

    Editing works a treat. BEWARE:
    1 - You MUST update the COUNT Property as in the snippet below if you add/delete aliases by hand (2nd line)
    2 - Make sure that Management Studio is CLOSED DOWN when you hand-edit the file or MS will overwrite your file when it next closes and you'll be out all your changes :cry: :

    XML from EngineOptions.xml file: 
    <UserDefinedAliases>
    <Count>132</Count>
    <UserDefinedAlias>
      <ObjectName>AddressBook</ObjectName>
      <Alias>ab</Alias>
    </UserDefinedAlias>
...
  </UserDefinedAliases>

    If anyone else uses Extended Properties, here's the query I use to generate the XML (send results to TEXT to retain the line feed & return characters):
    SELECT N'    <UserDefinedAlias>'
     + CHAR(13) + CHAR(10)
     + N'      <ObjectName>' + objname + N'</ObjectName>'
     + CHAR(13) + CHAR(10)
     + N'      <Alias>' + CAST(value AS NVARCHAR(10)) + N'</Alias>'
     + CHAR(13) + CHAR(10)
     + N'    </UserDefinedAlias>'
  FROM ::fn_listextendedproperty(default, 'user', 'dbo', 'table', default, default, default)
 WHERE NAME = N'TableAlias'
 ORDER BY 1

    Hack as necessary!!!

    The CAST(value AS nvarchar(10)) is needed to stop the precompiler barfing on the query!
    PDinCA
    0

Add comment

Please sign in to leave a comment.