Let's imagine that you are working in SQL Server Management Studio and you wish to see descriptions of tables and columns (the one provided by the architect). You can use extended properties for this and standard SSMS features but editing and browsing is a nightmare:

You can use Dataedo tool to conveniently edit and browse the documentation. But that needs switching to another program and if there are 10, 20 or 30 objects you are looking for it might not be convenient. We created a simple solution to browse Dataedo documentation from within SQL Server Management Studio with a press of shortcut keys.

There are some steps you need to perform to set this up.

1. Create Linked Server (optional)

If your Dataedo repository is located on a different server than your database, you must create a Linked Server (on the server you want to browse documentation from). Provide necessary information to create the connection. In the "Data source" field enter the address of the server where Dataedo repository is located.

Now move to the Security tab, choose Be made using this security context and type in the login credentials to the server which you want to access. Press OK once you finish.

2. Create stored procedure

To be able to get documentation you need to create standard stored procedure in each database you want to check your documentation of. There is a bit simpler, but not recommended, method with one procedure in master database. Please scroll below for details.

You need to provide the name of Dataedo repository database (by default this is "dataedo") and linked server name (if you created it).

If you have Dataedo repository on a separate server use database link name (and replace linked_server):

  SET @DataedoDatabase = 'linked_server.dataedo.dbo'  

If you have Dataedo repository on the same server use simplified format:

  SET @DataedoDatabase = 'dataedo.dbo'  

Change those elements and run this script in the database you want to use it from:

CREATE procedure [dbo].[get_dataedo_documentation] ( @ObjectName varchar(128)  ) AS
BEGIN
  SET NOCOUNT ON 

  DECLARE @SQL nvarchar(4000); 
  DECLARE @DataedoDatabase varchar(100);
  DECLARE @ObjectsCounter int;    
  SET @ObjectName = replace(replace(@ObjectName, '[', ''), ']', '');
-- TODO: On different server : SET @DataedoDatabase = 'linked_server.dataedo.dbo'
-- On the same server: SET @DataedoDatabase = 'dataedo.dbo'
-- TODO: Replace 'dataedo' with your repository database name if you have changed it from the default
  SET @DataedoDatabase = 'linked_server.dataedo.dbo'  

  SET @SQL = 'SELECT 
     @counts = count(tables.name)
  FROM '+@DataedoDatabase+'.tables
  WHERE tables.name = ''' +@ObjectName+ ''' '
  EXECUTE sp_executesql @SQL, N'@counts int OUTPUT', @counts=@ObjectsCounter OUTPUT

  IF (@ObjectsCounter>=1) 
    BEGIN
      SET @SQL = 'SELECT
        tables.object_type,
        tables.[schema] as object_schema,
        tables.name as object_name,
        tables.title as object_title,
        tables.description_search as object_description
      FROM '+@DataedoDatabase+'.databases databases 
        INNER JOIN '+@DataedoDatabase+'.tables tables 
                            ON tables.database_id = databases.database_id
      WHERE 
        databases.name = db_name() 
        and tables.name = ''' + @ObjectName + ''' 
      ORDER BY 
        tables.object_type,
        tables.[schema]'
      EXEC (@SQL)

      SET @SQL = 'SELECT 
        CASE tables.object_type
            WHEN ''TABLE'' THEN ''TABLE_COLUMN''
            ELSE ''VIEW COLUMN''
        END AS object_type,
        tables.[schema] as object_schema,
        tables.name as object_name,
        columns.[name] column_name,
        columns.title column_title, 
        CASE unique_constraints.primary_key
            WHEN 1 THEN ''PK''
            WHEN 0 THEN ''UK''
            ELSE ''''
        END as [unique],
        ref_tables.[name] + ''.'' + ref_columns.[name] as reference,
        columns.[description] column_description     
      FROM '+@DataedoDatabase+'.databases databases
        INNER JOIN '+@DataedoDatabase+'.tables tables 
              ON tables.database_id = databases.database_id
        INNER JOIN '+@DataedoDatabase+'.columns columns 
              ON columns.table_id = tables.table_id
         LEFT JOIN '+@DataedoDatabase+'.unique_constraints_columns constraints_col 
              ON columns.column_id = constraints_col.column_id
         LEFT JOIN '+@DataedoDatabase+'.unique_constraints unique_constraints 
              ON unique_constraints.unique_constraint_id = constraints_col.unique_constraint_id
         LEFT JOIN '+@DataedoDatabase+'.[tables_relations_columns] 
              ON [tables_relations_columns].column_fk_id = columns.column_id
         LEFT JOIN '+@DataedoDatabase+'.[tables_relations] 
              ON tables_relations_columns.table_relation_id = [tables_relations].table_relation_id  
         LEFT JOIN '+@DataedoDatabase+'.tables ref_tables 
              ON [tables_relations].pk_table_id = ref_tables.table_id
         LEFT JOIN '+@DataedoDatabase+'.columns ref_columns 
              ON tables_relations_columns.column_pk_id = ref_columns.column_id
      WHERE 
        databases.name = db_name()
        and tables.name = ''' + @ObjectName + ''' 
      ORDER BY 
        tables.object_type,
        tables.[schema],
        columns.ordinal_position '  
      EXEC (@SQL)
    END


  SET @SQL = 'SELECT 
     @counts = count(procedures.name)
  FROM '+@DataedoDatabase+'.procedures
  WHERE procedures.name = ''' +@ObjectName+ ''' '
  EXECUTE sp_executesql @SQL, N'@counts int OUTPUT', @counts=@ObjectsCounter OUTPUT

  IF (@ObjectsCounter>=1)
    BEGIN
      SET @SQL = '      
      SELECT
        procedures.object_type,
        procedures.[schema] as object_schema,
        procedures.name as object_name,
        procedures.title as object_title,
        procedures.description_search as object_description
      FROM '+@DataedoDatabase+'.databases databases 
        INNER JOIN '+@DataedoDatabase+'.procedures procedures 
                            ON procedures.database_id = databases.database_id
      WHERE 
        databases.name = db_name() 
        and procedures.name = ''' + @ObjectName + ''' 
      ORDER BY
        procedures.object_type,
        procedures.[schema] '
      EXEC (@SQL)

      SET @SQL = ' 
      SELECT 
        CASE procedures.object_type
            WHEN ''PROCEDURE'' THEN ''PROCEDURE PARAMETER''
            ELSE ''FUNCTION PARAMETER''
        END AS object_type,
        procedures.[schema] as object_schema,
        procedures.name as object_name,
        param.[name] as parameter_name,
        param.parameter_mode, 
        param.[description] as parameter_description     
      FROM '+@DataedoDatabase+'.databases databases
        INNER JOIN '+@DataedoDatabase+'.procedures procedures 
                            ON procedures.database_id = databases.database_id
        INNER JOIN '+@DataedoDatabase+'.parameters param 
                            ON param.procedure_id = procedures.procedure_id
      WHERE 
        databases.name = db_name() 
        and procedures.name = ''' + @ObjectName + ''' 
      ORDER BY
        procedures.object_type,
        procedures.[schema],
        param.ordinal_position '
      EXEC (@SQL)
    END

END;

Global procedure

Solution above has some disadvantages - you need to create a stored procedure in your databases. Sometimes hat might not be possible. It is also not the most convenient if you have many databases. There is a workaround, but it's not recommended by Microsoft. You can create one stored procedure for all databases on the server in the master database. Its name must start with "sp_" so change procedure name from get_dataedo_documentation to sp_get_dataedo_documentation in the script above and execute it on master database. You could do it by adding this line in the beginning of the scipt:

  USE master;

Procedure also must be marked as a system object. Use script below to do it:

  EXEC sys.sp_MS_marksystemobject sp_get_dataedo_documentation

3. Create shortcuts

Now, you need a key shortcut to trigger the procedure. To create one:

• Go to Tools, click on the Options.
• Once the pop-up appears, move to Keyboard list on the left-hand menu. Unwind it and choose Query Shortcuts.
• Pick one of the available shortcuts and type in the name of the stored procedure. By default, its name is get_dataedo_documentation.

4. Access documentation

Done! Now when you select the name of an object and executed your defined shortcut, you will see comments from your Dataedo repository.

As you can see, besides viewing table and column descriptions you can also see keys and what tables columns refer to. This includes relations that do not have FKs defined.

That's it. Let us know what you think.