find out missing stored procedures in mssql

Question

I have one big projects which is worked by many developers. WE have git for version control of codes but we are having difficulty for version controls of tables and stored procedure. Everytime i have to take backup from main server and restore database on my local server but still there are 100+ stored procedures. I just need to sync my stored procedure with them and their with mine.

Is their any way to find out the stored procedure which is their server but non mine and vice versa?

all of my procedures start with sp_

Answer 1

The best way is as below

• Take back up from your server

• Restore it to your local server where your local database resides

• say db1 is the database you restored just now and db2 is the database you already have in local server

  If you need to find out the stored procedure which is in db1 but not in db2 then fire the sql below

SELECT SPECIFIC_NAME
FROM db1.information_schema.routines
WHERE routine_type = 'PROCEDURE'
  AND Left(ROUTINE_NAME, 3) IN ('sp_',
                                'xp_',
                                'ms_') EXCEPT
  SELECT SPECIFIC_NAME
  FROM db2.information_schema.routines WHERE routine_type = 'PROCEDURE'
  AND Left(ROUTINE_NAME, 3) IN ('sp_',
                                'xp_',
                                'ms_')

Answer 2

Hi You have to do this in two steps

1. Create a link to the server from your sql instance using the script below Note to change [Server IP] with your SQL Server IP also [User ID] and [Password] with valid SQL credentials.

   /* Object:  LinkedServer [Server IP]    Script Date: 12/12/2013 12:29:41 */
   EXEC master.dbo.sp_addlinkedserver
     @server = N'[Server IP]',
     @srvproduct=N'SQL Server'
   
   /* For security reasons the linked server remote logins password is changed with ## */
   EXEC master.dbo.sp_addlinkedsrvlogin
     @rmtsrvname=N'[Server IP]',
     @useself=N'False',
     @locallogin=NULL,
     @rmtuser=N'[User Id]',
     @rmtpassword='[Password]'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'collation compatible',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'data access',
     @optvalue=N'true'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'dist',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'pub',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'rpc',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'rpc out',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'sub',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'connect timeout',
     @optvalue=N'0'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'collation name',
     @optvalue=NULL
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'lazy schema validation',
     @optvalue=N'false'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'query timeout',
     @optvalue=N'0'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'use remote collation',
     @optvalue=N'true'
   
   GO
   
   EXEC master.dbo.sp_serveroption
     @server=N'[Server IP]',
     @optname=N'remote proc transaction promotion',
     @optvalue=N'true'
   
   GO 
   

2. Execute the following Query. Here change [Server IP] to correct IP and [Database] to your main server Database name. Execute this query on your local server.

       SELECT *
       FROM   [Server IP].[Database].sys.objects
       WHERE  type = 'p'
              AND name NOT IN (SELECT name
                               FROM   sys.objects)