How can I check the type of object associated with an object_id? (SQL Server 2012)

Question

I've searched through StackOverflow and Google for a while and didn't find anything too similar, so here's my problem:

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').

Here is my current code, which works, but I feel is a dirty solution:

SELECT DISTINCT
  'The Sequence column of the ' + CAST(OBJECT_NAME([AC].[object_id]) AS NVARCHAR(255)) + ' table is missing a Sequence>=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]>=(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%'

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?

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.

Thanks, Andrew

You can look up the type of an object_id in sys.objects. The different types are listed here: http://msdn.microsoft.com/en-us/library/ms190324.aspx — supergrady, Jul 19 '13 at 19:45

score 1 · Accepted Answer · answered Jul 19 '13 at 23:40

You may refer tables using sys.tables view and search within constraints associated to Sequence column solely:

select quotename(schema_name(t.schema_id)) + '.' + quotename(t.name)
from sys.tables t
    join sys.columns c on c.object_id = t.object_id
    left join sys.check_constraints cs on cs.parent_object_id = t.object_id and cs.parent_column_id = c.column_id
        and cs.definition like '%Sequence]>=(1))'
where c.name = 'Sequence' and cs.object_id is NULL

This should give you tables having Sequence column, but having no constraint on it or having constraint which is not defined according to the rule specified.

Thanks for the solution! This works perfectly, and is a lot cleaner than my code! — The Boondoggler, Jul 22 '13 at 12:46

How can I check the type of object associated with an object_id? (SQL Server 2012)

1 Answers1