sql server identify non unique tables and create non clustered indexes with identity property

create unique keys with non-clustered index on all the tables.

one of the audit tool which we are using requires a unique column must be present on the table to audit. Developed the below script to create a identity column with non-clustered index on all the tables , if the table has an identity column without any unique index then the script-2 will do this.

1)modify the tables to have a identity key columns on all the tables with non-clustered indexes
2)script:2- if the table has a identity key already exist but without any unique index then it will create the unique index on the identity key columns.

select   'alter table ['+SCHEMA_NAME(O.SCHEMA_ID)+'].'+o.name +' add IDENTITY_FOR_MASK int identity(1,1) constraint NCL_X_IDENTITY_FOR_MASK unique nonclustered' from sys.objects o join  sys.indexes i on o.object_id=i.object_id where o.type = 'u' AND objectproperty(o.object_id,'TableHasIdentity') =0 group by o.name,O.SCHEMA_ID having max(cast(I.is_unique as int))

select  'CREATE UNIQUE INDEX NCL_X_IDENTITY_FOR_MASK on ['+SCHEMA_NAME(O.SCHEMA_ID)+'].'+O.NAME+'('+IC.NAME+')' from sys.objects o join  sys.indexes i on o.object_id=i.object_id join sys.identity_columns  IC on o.object_id = IC.object_id where o.type = 'u' AND objectproperty(o.object_id,'TableHasIdentity') =1 group by o.name,IC.NAME,O.SCHEMA_ID having max(cast(I.is_unique as int))