Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Can you use T-SQL to show you all SP with joins

Answered Can you use T-SQL to show you all SP with joins

  • Tuesday, January 29, 2013 9:30 PM
     
     
    Sign In to Vote
    0

    I'm fairly new to the whole world of SQL but I was wondering if you can write something that will check all the SP's in a database for any that contain joins of any kind, then output the

    • Table name
    • Type of join
    • Which tables it joins

    I'm in need to learn this anyway so any help would be great.

    Regards

    If this is helpful please mark it so. Also if this solved your problem mark as answer.


    • Edited by BHBA Tuesday, January 29, 2013 10:10 PM Error in title
    •  
     

All Replies

  • Tuesday, January 29, 2013 9:37 PM
    Moderator
     
     
    Sign In to Vote
    0
    Do you mean you want to output tables having foreign keys? The table is a single entity, it does not contain any joins. Are you talking about views and view definitions?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Tuesday, January 29, 2013 9:42 PM
    Moderator
     
     
    Sign In to Vote
    0

    You can use SSMS diagram tool to learn about  a database:

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: SQL Server 2012 Pro


     
  • Tuesday, January 29, 2013 9:55 PM
     
     
    Sign In to Vote
    0
    I didnt mean a table sorry, I meant a Stored Procedure. Forgive my stupidity  

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

     
  • Tuesday, January 29, 2013 10:36 PM
    Moderator
     
     
    Sign In to Vote
    1

    I see - this is not a simple question then. There is nothing built-in. This particular problem has been discussed in this forum few months back, try searching.

    The best you can do is to see if the SP includes word JOIN. You will not be able to actually identify the tables, etc. because writing a parser is not a simple task.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Tuesday, January 29, 2013 10:45 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Naomi,

    I did just that and found a free SQL Search tool to do it... BUT I also found the following T-SQL:

    SELECT OBJECT_NAME(id) 
FROM syscomments 
WHERE [text] LIKE '%JOIN%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)
    This seems to list the same the SQL search does.

    If this is helpful please mark it so. Also if this solved your problem mark as answer.

    • Marked As Answer by BHBA Tuesday, January 29, 2013 10:52 PM
    •  
     
  • Tuesday, January 29, 2013 10:50 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    Yes, basically that's all you can do - identify procedures having word JOIN in them.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked As Answer by BHBA Tuesday, January 29, 2013 10:52 PM
    •