Thanks Lyon. But then how would I have the structure of the underlying object that is getting changed available in the DDL trigger ?

Yes Steve any entities created under the dbo schema should auto replicate across all the schemas that exist in the database.We have a database with multiple schemas and the reason...

Unfortunately synonyms won't work in my case..

Suppose I have schema1 and schema2 in my database. So when I create a table say T under dbo schema,schema1.T and schema2.T should get created. It does not have to...

sgmunson (12/2/2016)

---

Glad I could help. Be sure to look up how LEAD and LAG work. They both allow for an optional "default" value, which is chosen...

@sgmunson

Wow...Fantastic..This is exactly what I need...

I would show this solution to the management so they understand how tough the solution is and I am sure it would take...

John Mitchell-245523 (12/2/2016)

---

Yes, the answer was kind of "that's the requirement", which is fair enough, I suppose. I'd still love to know what this is for, though. The...

J Livingston SQL (12/2/2016)

---

maybe...???

WITH C1

AS (SELECT id,

p2pid,

...

Yes we do have an identity column in the table.What I meant about data not being in order was that the data does not have a specific order for p2pid...

This data format comes through a 3rd party application as is and gets dumped into the table without any specific order.

Yes if the values 124 and 125 don't match it...

Unfortunately there is no predefined order.The design is not in our hand.Best way I could think of ordering was something like this.

drop table #t

create table #t(id intidentity,p2pid int,p2psource int)

insert into...

Thanks but I doubt the gaps/island solution will...