title: Optimization for system views
body: <p>I have a query for getting columns data from the Microsoft SQL Server database. I need to speed up my query because I will run it multiple times each minute. Currently, my query took about 60ms to execute on SQL Server. It would be awesome too cut it down to 30 or 40ms. </p>
  
  <p>Execution plan: <a href="https://www.brentozar.com/pastetheplan/?id=HyF8enSdL" rel="nofollow noreferrer">plan</a></p>
  
  <p>Is there any possible way for optimization?</p>
  
  <pre><code>SELECT 
      c.name as 'columnName',
      t.name as 'dataType',
      t.name + '(' + cast(c.max_length as varchar(50)) +')' AS 'dataTypeFull', 
      ISNULL(sep.value,'') AS 'columnDescription',
      CASE 
        WHEN fk.object_id is not null THEN fk.name ELSE NULL 
      END AS 'foreignKey',
      pk_tab.*,
      ISNULL(i.is_primary_key,0) AS 'primaryKey'
    FROM
      sys.tables tab
    INNER JOIN 
      sys.columns c ON c.object_id = tab.object_id
    LEFT OUTER JOIN 
      sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN 
      sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    LEFT OUTER JOIN 
      sys.foreign_key_columns fk_cols ON fk_cols.parent_object_id = tab.object_id
                  AND fk_cols.parent_column_id = c.column_id
    LEFT OUTER JOIN 
      sys.foreign_keys fk ON fk.object_id = fk_cols.constraint_object_id
    LEFT OUTER JOIN 
      sys.tables pk_tab ON pk_tab.object_id = fk_cols.referenced_object_id
    LEFT JOIN 
      sys.extended_properties sep ON c.object_id = sep.major_id 
                  AND c.column_id = sep.minor_id 
                  AND sep.name = 'MS_Description'
    JOIN 
      sys.types t ON c.user_type_id = t.user_type_id
    WHERE 
      c.object_id = Object_id('some-table-name')
  </code></pre>