October 3, 2005 at 10:28 am
Need SQL statement to perform the following:
Select all USER Stored Procs from Server_S1 Database_D1 that do not exist on Server_S2 Database_D2.
thx in advance
October 3, 2005 at 10:46 am
Try this - untested, but should get you going:
select r1.*
from server1.database1.information_schema.routines r1
left join server2.database2.information_schema.routines r2
on r1.routine_name = r2.routine_name and r1.routine_type = r2.routine_type
where r1.routine_type = 'procedure' and (r2.routine_name is null)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 4, 2005 at 1:31 pm
You'll probably have problems with using information_schema views as a linked server reference. It may work with the master db but not others.
Replace the four-part name with an OPENQUERY function.
select r1.routine_name, r1.routine_type
from openquery(server1, 'select routine_name, routine_type from database1.information_schema.routines') r1
left join openquery(server2, 'select routine_name, routine_type from database2.information_schema.routines') r2
on r1.routine_name = r2.routine_name and r1.routine_type = r2.routine_type
where r1.routine_type = 'procedure' and (r2.routine_name is null)
October 4, 2005 at 2:09 pm
I use the following as a quick check to track differences of all user objects. Note: the servers must be linked.
Hope you fine this useful.
select
a.name as [Not in Database2],
b.name as [Not in Database1] ,
(Case isnull(a.xType,b.xType)
When 'U' Then 'Table'
When 'P' Then 'Stored Procedure'
When 'V' Then 'View'
When 'FN' Then 'User Defined Function'
End) as [Object Type]
from server1.database1.dbo.sysobjects a
full join server2.database2.dbo.sysobjects b
on b.name=a.name
where
((a.xtype='P' or a.xtype='U' or a.xtype='FN' or a.xtype='V') and b.name is null)
or
((b.xtype='P' or b.xtype='U' or b.xtype='FN' or b.xtype='V') and a.name is null)
order by isnull(a.xType,b.xType)
Redgate's sql compare is also useful for diffing db's.
October 4, 2005 at 2:24 pm
Don't forget xtypes IF and TF (table-valued functions).
October 4, 2005 at 2:34 pm
Good catch, I need to add them. Thanks!
October 4, 2005 at 3:24 pm
You all have been more than helpful. Question ~ when you mentioned 'Dont Forget 'IF' and 'TF' --- should I simply modify
((a.xtype='P' or a.xtype='U' or a.xtype='FN' or a.xtype='V')
to look like:
((a.xtype='P' or a.xtype='U' or a.xtype='FN' or a.xtype='V' or a.xtype='IF' or a.xtype='TF' )
October 4, 2005 at 4:06 pm
Yes. Either that or "a.xtype in ('P','U','V','FN','IF','TF')"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply