Comparing stored procs between two different serve

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing stored procs between two different serve

Author

Topic

SKP
Starting Member

34 Posts

Posted - 2007-02-08 : 06:01:22

Hi

I am looking for a way to compare list of stored procedures between two servers, is there a standard tool or function that i can use?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 06:10:21

Is another server added as a linked server? If yes, then you can simply use INFORMATION_SCHEMA.ROUTINES view to list the SPs on both servers.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 06:11:00

this will give u the lists of stored procedures


select 	* 
from 	sys.objects 
where	type	= 'P'

SKP
Starting Member

34 Posts

Posted - 2007-02-08 : 06:19:41

hi Harsh

other server is not set up as linked server.

Any other ways mate?

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-08 : 06:20:19

then can you set up the linked server ?

SKP
Starting Member

34 Posts

Posted - 2007-02-08 : 06:26:20

Yes I have set up the linked server, now?

Shall I just run Select * from INFORMATION_SCHEMA.ROUTINE ,which field will let me decide whether the objects are identical or not?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 06:26:36

If you are not going to access the server frequently:

Select routine_name
from
OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=<value>;User ID=<value>;Password=<value>'
         ).db.information_schema.routines
Where Routine_Type = 'PROCEDURE'

For Linked servers:

Select Routine_Name from LinkedServer1.db.information_schema.routines
Where Routine_Type = 'PROCEDURE'
GO
Select Routine_Name from LinkedServer2.db.information_schema.routines
Where Routine_Type = 'PROCEDURE'
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SKP
Starting Member

34 Posts

Posted - 2007-02-08 : 06:31:44

OK, this will give me the name of the routine, how ill I know whether these routines are identical or not?

Thanks you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources