title: How can I check the type of object associated with an object_id? (SQL Server 2012)
body: <p>I've searched through StackOverflow and Google for a while and didn't find anything too similar, so here's my problem:</p>
  
  <p>I'm currently writing a stored procedure to check that every every column in a database named 'Sequence' has an associated constraint ensuring the value is >=1. However, my current method returns all objects containing 'Sequence', not just tables (ie. get/set/delete stored procedures that contain 'Sequence').</p>
  
  <p>Here is my current code, which works, but I feel is a dirty solution:</p>
  
  <pre><code>SELECT DISTINCT
    'The Sequence column of the ' + CAST(OBJECT_NAME([AC].[object_id]) AS NVARCHAR(255)) + ' table is missing a Sequence&gt;=1 constraint.' AS MESSAGE
  FROM [sys].[all_columns] AC
  LEFT JOIN [sys].[check_constraints] CC
  ON [CC].[parent_object_id] = [AC].[object_id]
    AND [CC].[name] LIKE '%Sequence'
    AND [CC].[definition] LIKE '%Sequence]&gt;=(1))'
  WHERE [AC].[name] = 'Sequence'
    AND [CC].[name] IS NULL
    AND OBJECT_NAME([AC].[object_id]) NOT LIKE '%Get%'
    AND OBJECT_NAME([AC].[object_id]) NOT LIKE '%Set%'
    AND OBJECT_NAME([AC].[object_id]) NOT LIKE '%Delete%'
  </code></pre>
  
  <p>Specifically, my question is: Given [sys].[all_columns].[object_id], is there an easy way to check if the given object is a table versus a stored procedure?</p>
  
  <p>Any help or advice on this would be greatly appreciated! Also general code cleanup here, I'm relatively new to tSQL so this is probably not the most efficient way to go about it.</p>
  
  <p>Thanks,
  Andrew</p>