Calling one sp from another

Question

On this link

http://gallery.technet.microsoft.com/Compare-Data-0c5bfc87#content

we can find a stored procedure example which can compare two tables data. WHat I would like is to call this sp for each table in database. I have found next sp that will enumerate through all tables

http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx

The problem is that I cannot get to pass properly parameters. Here is what I tried (I have placed both databases on local server):

Exec sp_MSforeachtable "EXEC sp_CompareTable dbName1, dbName2, NULL, PARSENAME('?', 1)"

and that fails with

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '[dbo].[Activities]'.

And the same message for every table. Can anyone help me with what I am doing wrong here?

this from your link: "We could, of course, create a script that uses a cursor to loop through all of the tables in the database to do our bidding through dynamic SQL. Yuck! " - That would be my very suggestion: use a cursor and omit this magick undocumented stuff. Best regards — Enno, Jun 01 '13 at 21:59

Fruitbat · Accepted Answer · 2013-06-02T16:11:08.720

I'll stick my neck out and post this as an answer because it's not formatted nicely as a comment. Have you tried this:

sp_MSforeachtable "EXEC sp_CompareTable dbName1, dbName2, NULL, PARSENAME('[?]', 1)"

Update:

It looks like it doesn't like the PARSENAME. You could try this (I tried this on a version of sp_CompareTable with the EXEC changed to a PRINT).

Add this line to sp_CompareTable (before the EXEC):
```
SET @TableName = PARSENAME(@TableName,1)
```

Call it like this:

sp_MSforeachtable "EXEC sp_CompareTable dbName1, dbName2,  dbo, '?'"

NB: This would be a quick fix for the case where you only have the "dbo" schema. It doesn't really answer the question of exactly why the original syntax doesn't work.

Update Again:

Here's a version of the Compare Table stored procedure which is tailored to run with sp_MSforeachtable

CREATE PROC [dbo].[uspCompareTable](@db1 varchar(250), @db2 sysname, @TableName sysname) 
AS 

declare @reason varchar(7)='Missing'; 

IF @TableName = '[dbo].[sysdiagrams]'
    RETURN

IF CHARINDEX('.',@db1,1) <> 0 
    SET @db1=QUOTENAME(SUBSTRING(@db1,1, CHARINDEX('.',@db1,1)-1))+'.'+QUOTENAME(SUBSTRING(@db1, CHARINDEX('.',@db1,1)+1,DATALENGTH(@db1)-CHARINDEX('.',@db1,1))) 

IF CHARINDEX('.',@db2,1) <> 0 
    SET @db2=QUOTENAME(SUBSTRING(@db2,1, CHARINDEX('.',@db2,1)-1))+'.'+QUOTENAME(SUBSTRING(@db2, CHARINDEX('.',@db2,1)+1,DATALENGTH(@db2)-CHARINDEX('.',@db2,1))) 

EXEC ('
SELECT * FROM  
  (SELECT * FROM '+ @db1 + '.' + @TableName +'  
   EXCEPT 
     SELECT * FROM '+ @db2 + '.' + @TableName +') T 
     CROSS JOIN (SELECT '''+@reason +' in '+@db2 +'.' + @TableName+''' Reason) T2 
UNION ALL 
SELECT * FROM  
  (SELECT * FROM '+ @db2 + '.' + @TableName +'  
   EXCEPT 
     SELECT * FROM '+ @db1 + '.' + @TableName +' ) T 
     CROSS JOIN (SELECT ''' + @reason + ' in ' + @db1 + '.' + @TableName + ''' Reason) T2')

Here I'm assuming that schema will be part of the TableName (which it should be if you're calling from sp_MSforeachtable). Also a tweak to skip sysdiagrams which gets picked up on my system (SQL Server 2008 Express).

Usage would be

sp_MSforeachtable "EXEC uspCompareTable dbname1, dbname2, '?'"

Incorrect syntax near '[[dbo]].[Activities]]]'. Just more brackets as a result — Goran, Jun 02 '13 at 10:34
If you've only got "dbo", you could try my quick fix (updated above). — Fruitbat, Jun 02 '13 at 12:33
Not really relevant to the question but perhaps worth a mention. You shouldn't really use the prefix "sp_" on a stored procedure. I suspect that a proper fix to this would involve rewriting sp_CompareTable (I may look at this later) and in that case it should be renamed as well. — Fruitbat, Jun 02 '13 at 13:09

Calling one sp from another

1 Answers1