Browse Dataedo Database Documentation in SQL Server Managment Studio (SSMS)

Magda Lakomiec - Dataedo Team Magda Lakomiec 2017-10-24

Table of Contents:


    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.

    0
    There are no comments. Click here to write the first comment.