4

Let's say I have x number of stored procs in my SQL Server Project in Visual Studio 2015.

e.g. 5 stored procedures.

I want to do a comparison between these 5 stored procedures and what's on my database server. I just want to compare what differences there are between the 5 stored procedures in my project and of that in the database similar to a schema compare.

This project is not linked to any database at the moment because I am worried it will screw up the existing database. The existing database has hundreds of stored procedures but I'm only interested in finding out the difference between these X or 5 stored procedures.

Is this possible using SQL Server project?

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
SamIAm
  • 1,805
  • 4
  • 22
  • 41
  • 1
    Would an [online diff tool](https://www.diffchecker.com/) be of any use to you for comparing just 5 stored procedures? – Tim Biegeleisen Dec 16 '15 at 05:42
  • It's not really 5 stored procs. It's usually 50 + and ijust used 5 as an arbitrary number. – SamIAm Dec 16 '15 at 05:43
  • So you want to compare 50x50? What are you looking for? – Tim Biegeleisen Dec 16 '15 at 05:43
  • So essentially, say I have 30 SQL scripts in my project. I use this project as a way to version control my scripts. However, we update scripts through SSMS so I want a way to know which scripts have changed and is different from what is in my SQL Server Project. I only want the differences of the script in my Project and nothing else. – SamIAm Dec 16 '15 at 05:49
  • This is a little side note. A great tool I use to compare two sotred procedures is UltraCompare. You get I think 30 free days when you download it and if you like enough maybe you cna talk someone into a license. – Wes Palmer Jan 06 '16 at 20:57
  • Duplicate question: check here http://stackoverflow.com/questions/685053/what-is-best-tool-to-compare-two-sql-server-databases-schema-and-data – alessalessio Jan 18 '16 at 09:17
  • I've done something like this by exporting the X number of scripts in sys.all_sql_modules.definition to text files, then using BeyondCompare to compare the db script file to the project script file. Clumsy and tedious, but for my one-off assignment it worked better than other attempts. – Jim the Frayed Jan 27 '16 at 17:41
  • Note: I DO NOT work for this company. But I used this product extensively in a smaller company when we had to do source diffs on SQL, anything from stored procedures to table schemas, and it worked fast and accurately, including when required to do full table rebuilds. http://www.red-gate.com/products/sql-development/sql-compare/ – Patrick Jun 27 '16 at 02:57

2 Answers2

0

There are many ways to compare store procedures in SQL Server.

  1. compare scripts of two procedures

select procedure script by using the following query

 SELECT OBJECT_DEFINITION(p.object_id) FROM sys.procedures p

& then compare with script of another procedure.

2 : 

 SELECT p.modify_date FROM sys.procedures p

select modify date of procedure and compare them.

porges
  • 28,750
  • 3
  • 83
  • 112
The Computer Scientist
  • 120
  • 13
0

I'd try to make a script to dump all procedure's definitions that you want to compare on the database to separate files named with the procedure's names and then I'd make another script to run a diff between the scripts dumped in a specific directory vs the scripts under version control and direct the output to a file. Maybe using PowerShell is a good way to go to do it or even batch. All that this script should do is to iterate through the directory contents of the dumped scripts and fetch the script with the same name on the version controlled directory tree, and the script would compare one by one directing the outputs to a file showing the diffs.

Leandro Jacques
  • 363
  • 3
  • 14