{"id":8076,"date":"2015-06-22T04:14:52","date_gmt":"2015-06-22T04:14:52","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-use-objectproperty-to-locate-problems-in-tables\/"},"modified":"2016-07-28T10:56:43","modified_gmt":"2016-07-28T10:56:43","slug":"how-to-use-objectproperty-to-locate-problems-in-tables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/other\/how-to-use-objectproperty-to-locate-problems-in-tables\/","title":{"rendered":"How to use ObjectProperty to locate problems in tables"},"content":{"rendered":"<p>There are plenty of very useful object properties that can help us to find problems in our databases. We can use object properties to find tables without primary key, tables without clustered index and much more information.<\/p>\n<p>Object properties simplifies our queries, without them we would need to do much more complex queries over DMV&#8217;s to find the same information.<\/p>\n<p>For example, to find if the table has clustered index, primary key, timestamp and identity, we can use the following query:<\/p>\n<pre class=\"csharpcode\">select name,objectproperty(object_id,'TableHasClustIndex') HasClusteredIndex,\nobjectproperty(object_id,'TableHasPrimaryKey') HasPrimaryKey,\nobjectproperty(object_id,'TableHasTimestamp') HasTimeStamp,\nobjectproperty(object_id,'TableHasPrimaryKey') HasIdentity\nfrom sys.objects\nwhere objectproperty(object_id,'IsUserTable')=1<\/pre>\n<\/p>\n<p>We can filter the information to get only tables that has some problems, like tables without primary keys or without clustered index:<\/p>\n<pre class=\"csharpcode\">with qry as (\nselect name,objectproperty(object_id,'TableHasClustIndex') HasClusteredIndex, \n     objectproperty(object_id,'TableHasPrimaryKey') HasPrimaryKey, \n     objectproperty(object_id,'TableHasTimestamp') HasTimeStamp, \n     objectproperty(object_id,'TableHasPrimaryKey') HasIdentity \nfrom sys.objects where objectproperty(object_id,'IsUserTable')=1) \nselect Name,HasClusteredIndex,HasPrimaryKey, HasTimeStamp,HasIdentity \nfrom qry \nwhere HasClusteredIndex=0 or HasPrimaryKey=0<\/pre>\n<p>We can create even a better solution with a table valued function that we can re-use:<\/p>\n<pre class=\"csharpcode\">CREATE FUNCTION dbo.TableInformation() \nRETURNS TABLE AS \nRETURN ( \nselect name,objectproperty(object_id,'TableHasClustIndex') HasClusteredIndex, \n      objectproperty(object_id,'TableHasPrimaryKey') HasPrimaryKey, \n      objectproperty(object_id,'TableHasTimestamp') HasTimeStamp, \n      objectproperty(object_id,'TableHasPrimaryKey') HasIdentity \nfrom sys.objects \nwhere objectproperty(object_id,'IsUserTable')=1 )<\/pre>\n<p>We can use these queries together a DDL trigger to disallow the creation of tables without primary key. Let&#8217;s see how this trigger will work:<\/p>\n<pre class=\"csharpcode\">Create Trigger CheckTablePK\nON Database\nFOR Create_Table,Alter_Table\nAs\nBegin\n    Set nocount on\n    declare @EventData xml\n    declare @SchemaName sysname\n    declare @ObjectName sysname\n    declare @FullName varchar(100)\n\n    -- Retrieve information about the event\n    set @EventData= EventData()\n    -- Retrieve the schema of the object\n    set @schemaName=@EventData.value('(\/EVENT_INSTANCE\/SchemaName)[1]','sysname')\n    -- Retrieve the object name\n    set @ObjectName= @EventData.value('(\/EVENT_INSTANCE\/ObjectName)[1]','sysname')\n    -- Build the full object name, taking care of special characters in the names\n    set @FullName=QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)\n\n    -- Test if the object has primary key\n    IF ObjectProperty(Object_ID(@FullName),'TableHasPrimaryKey')&lt;&gt;1\n    Begin\n        Print 'This table needs a primary key'\n        Rollback Tran\n    end\nend<\/pre>\n<p>You can see a list with all the properties at https:\/\/msdn.microsoft.com\/pt-br\/library\/ms176105.aspx<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are plenty of very useful object properties that can help us to find problems in our databases. We can use object properties to find tables without primary key, tables without clustered index and much more information. Object properties simplifies our queries, without them we would need to do much more complex queries over DMV&#8217;s&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-8076","post","type-post","status-publish","format-standard","hentry","category-other"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8076"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8076\/revisions"}],"predecessor-version":[{"id":42496,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8076\/revisions\/42496"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8076"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}