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

i've searched through stackoverflow , google while , didn't find similar, here's problem:

i'm writing stored procedure check every every column in database named 'sequence' has associated constraint ensuring value >=1. however, current method returns objects containing 'sequence', not tables (ie. get/set/delete stored procedures contain 'sequence').

here current code, works, feel dirty solution:

select distinct   'the sequence column of ' + cast(object_name([ac].[object_id]) nvarchar(255)) + ' table missing sequence>=1 constraint.' message [sys].[all_columns] ac left join [sys].[check_constraints] cc on [cc].[parent_object_id] = [ac].[object_id]   , [cc].[name] '%sequence'   , [cc].[definition] '%sequence]>=(1))' [ac].[name] = 'sequence'   , [cc].[name] null   , object_name([ac].[object_id]) not '%get%'   , object_name([ac].[object_id]) not '%set%'   , object_name([ac].[object_id]) not '%delete%' 

specifically, question is: given [sys].[all_columns].[object_id], there easy way check if given object table versus stored procedure?

any or advice on appreciated! general code cleanup here, i'm relatively new tsql not efficient way go it.

thanks, andrew

you may refer tables using sys.tables view , search within constraints associated sequence column solely:

select quotename(schema_name(t.schema_id)) + '.' + quotename(t.name) 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 , cs.parent_column_id = c.column_id         , cs.definition '%sequence]>=(1))' c.name = 'sequence' , cs.object_id null 

this should give tables having sequence column, having no constraint on or having constraint not defined according rule specified.