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’s to find the same information.
For example, to find if the table has clustered index, primary key, timestamp and identity, we can use the following query:
|
1 2 3 4 5 6 |
select name,objectproperty(object_id,'TableHasClustIndex') HasClusteredIndex, objectproperty(object_id,'TableHasPrimaryKey') HasPrimaryKey, objectproperty(object_id,'TableHasTimestamp') HasTimeStamp, objectproperty(object_id,'TableHasPrimaryKey') HasIdentity from sys.objects where objectproperty(object_id,'IsUserTable')=1 |
We can filter the information to get only tables that has some problems, like tables without primary keys or without clustered index:
|
1 2 3 4 5 6 7 8 9 |
with qry as ( select name,objectproperty(object_id,'TableHasClustIndex') HasClusteredIndex, objectproperty(object_id,'TableHasPrimaryKey') HasPrimaryKey, objectproperty(object_id,'TableHasTimestamp') HasTimeStamp, objectproperty(object_id,'TableHasPrimaryKey') HasIdentity from sys.objects where objectproperty(object_id,'IsUserTable')=1) select Name,HasClusteredIndex,HasPrimaryKey, HasTimeStamp,HasIdentity from qry where HasClusteredIndex=0 or HasPrimaryKey=0 |
We can create even a better solution with a table valued function that we can re-use:
|
1 2 3 4 5 6 7 8 9 |
CREATE FUNCTION dbo.TableInformation() RETURNS TABLE AS RETURN ( select name,objectproperty(object_id,'TableHasClustIndex') HasClusteredIndex, objectproperty(object_id,'TableHasPrimaryKey') HasPrimaryKey, objectproperty(object_id,'TableHasTimestamp') HasTimeStamp, objectproperty(object_id,'TableHasPrimaryKey') HasIdentity from sys.objects where objectproperty(object_id,'IsUserTable')=1 ) |
We can use these queries together a DDL trigger to disallow the creation of tables without primary key. Let’s see how this trigger will work:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Create Trigger CheckTablePK ON Database FOR Create_Table,Alter_Table As Begin Set nocount on declare @EventData xml declare @SchemaName sysname declare @ObjectName sysname declare @FullName varchar(100) -- Retrieve information about the event set @EventData= EventData() -- Retrieve the schema of the object set @schemaName=@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','sysname') -- Retrieve the object name set @ObjectName= @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname') -- Build the full object name, taking care of special characters in the names set @FullName=QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) -- Test if the object has primary key IF ObjectProperty(Object_ID(@FullName),'TableHasPrimaryKey')<>1 Begin Print 'This table needs a primary key' Rollback Tran end end |
You can see a list with all the properties at https://msdn.microsoft.com/pt-br/library/ms176105.aspx
Load comments