October 5, 2017 at 12:30 pm
We have 2 different same stored procedures in 2 different databases .We want to see the changes done before and after for SP .Can you please provide the query which can be seen text changes done in SP .
Thanks in Advance
October 5, 2017 at 12:36 pm
mytesting9 - Thursday, October 5, 2017 12:30 PMWe have 2 different same stored procedures in 2 different databases .We want to see the changes done before and after for SP .Can you please provide the query which can be seen text changes done in SP .Thanks in Advance
Not possible unless there is some kind of auditing in place
😎
If you have a backup trail, then you can restore to previous and do a comparison, there is nothing that will keep the history by default.
October 5, 2017 at 12:52 pm
Eirikur Eiriksson - Thursday, October 5, 2017 12:36 PMmytesting9 - Thursday, October 5, 2017 12:30 PMWe have 2 different same stored procedures in 2 different databases .We want to see the changes done before and after for SP .Can you please provide the query which can be seen text changes done in SP .Thanks in Advance
Not possible unless there is some kind of auditing in place
😎If you have a backup trail, then you can restore to previous and do a comparison, there is nothing that will keep the history by default.
I was trying this ...Can you pls help...
select *
from DB1.sys.all_objects O1
inner join DB2.sys.all_objects O2 on O1.name = O2.name
inner join DB1.sys.syscomments C1 on O1.object_id = C1.id
inner join DB2.sys.syscomments C2 on O2.object_id = C2.id
inner join DB1.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join DB2.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and
-- remove the line below if you want to search all objects
O1.type = 'P' and O1.name='spname'
October 6, 2017 at 9:00 am
mytesting9 - Thursday, October 5, 2017 12:52 PMEirikur Eiriksson - Thursday, October 5, 2017 12:36 PMmytesting9 - Thursday, October 5, 2017 12:30 PMWe have 2 different same stored procedures in 2 different databases .We want to see the changes done before and after for SP .Can you please provide the query which can be seen text changes done in SP .Thanks in Advance
Not possible unless there is some kind of auditing in place
😎If you have a backup trail, then you can restore to previous and do a comparison, there is nothing that will keep the history by default.
I was trying this ...Can you pls help...
select *
from DB1.sys.all_objects O1
inner join DB2.sys.all_objects O2 on O1.name = O2.name
inner join DB1.sys.syscomments C1 on O1.object_id = C1.id
inner join DB2.sys.syscomments C2 on O2.object_id = C2.id
inner join DB1.sys.schemas S1 on O1.schema_id = S1.schema_id
inner join DB2.sys.schemas S2 on O2.schema_id = S2.schema_id
where C1.text <> C2.text and
-- remove the line below if you want to search all objects
O1.type = 'P' and O1.name='spname'
The only way this can work is if both databases are on the same server, and at a point in time when one database has been changed and the other has not. If someone changes both databases in short order, you'll never see it from this query. You would actually need some kind of database trigger that would record changes to stored procedures in some kind of audit table. I'm not the expert on database triggers, so someone else may need to chime in, or you'll need to Google it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 11:17 am
This link has a good example of using a DDL trigger to capture changes to stored procedures:
SQL Server DDL Triggers to Track All Database Changes
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply