0

I am trying to run a system SP (which is a pre-built) using python pyodbc. In fact I am trying to see the dependencies on a table using sp_depends '<Object name>';

I am using the below code snippet.

df_f=[]
l_table = ['table_1','table_2','table_3']
try:
  for l in l_table:
    sql = """EXEC sp_depends '{0}';""".format(l)
    while cur.nextset():
        cur.execute(sql)
        c = cur.fetchall()
        df_l= pd.DataFrame.from_records(c, columns = [desc[0] for desc in cur.description])
        df_l['Referenced_Object'] = l
        df_f.append(df_l)
        break
except pyodbc.Error as err:
  s = str(err)
  print(s)
finally:
  cur.close()
  cnxn.close()

The above code is not running. It is not throwing error but not appending anything in df_f.

If I run the above SP separately, I am getting the below error:

ProgrammingError: No results.  Previous SQL was not a query.

I have taken help from this SO thread.

I am not able to SET NOCOUNT ON in this SP as this is a built-in and therefore I am not able to get the desired information in dataframe.

Any clue on this?

pythondumb
  • 1,187
  • 1
  • 15
  • 30
  • FYI, `sp_depends` is deprecated anyway, so perhaps you should be looking at an alternative system object. See the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql?view=sql-server-ver15) for more information. If you use the objects that are replacing it, the whole problem of `SET NOCOUNT` not being set to `ON` goes away. – Thom A Jan 11 '22 at 11:57
  • 1
    Also, I **strongly** suggest you learn to parametrise; the above is wide open to SQL Injection attacks. – Thom A Jan 11 '22 at 11:59
  • @Larnu:Didn't quite understand the `parametrise` part. I know about `sys.dm_sql_referencing_entities` but this doesn't give the result as `sp_depends` give. I mean I need the output exactly same as `sp_depends`. – pythondumb Jan 11 '22 at 12:07
  • I suggest a read of [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) If you aren't aware of parametrisation, you ***need*** to go back to the basic of SQL and interacting with it your from programming layer; it is a fundamental part of using an RDBMS. – Thom A Jan 11 '22 at 12:07
  • @Larnu: I am quite aware of parametrization in SQL server and I dont see this as a compelling need to implement in the OP. Can you please share your thoughts on any alternative approach of the OP? – pythondumb Jan 11 '22 at 12:12
  • 1
    *"I dont see this as a compelling need to implement in the OP."* Because injection is **dangerous**. It's the 2020's and it is *still* one of the most prolific and fatal security flaws out there. There is a reason companies are named, shamed and fined millions when they are foolish enough to suffer a breach due to things like SQL injection; because it's simple to parametrise queries and there have been enough incidents over the last 30 years that people have *no* excuse to follow such bad programming standards because they "didn't know better". – Thom A Jan 11 '22 at 12:15
  • Point taken. Will surely do the same when finally put the same on production. Thanks for sensitizing. – pythondumb Jan 11 '22 at 12:17
  • Any alternative approach on this? – pythondumb Jan 11 '22 at 12:20
  • As I mentioned, you'll want to use the new DMV's linked in the documentation. – Thom A Jan 11 '22 at 12:21

1 Answers1

0

As I mentioned in the comments, sp_depends has been deprecated; it should not be used. As per the documentation you should be using sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

You can get similar results to the sp_depends with the following queries. You would need to replace the variables with parameters from your programming language (not inject them):

SELECT CONCAT(re.referenced_schema_name,'.', re.referenced_entity_name) AS [name],
       o.type_desc AS [Type],
       CASE re.is_updated WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END AS updated,
       CASE re.is_selected WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END AS selected,
       re.referenced_minor_name AS [column]
FROM sys.dm_sql_referenced_entities(QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName) ,'OBJECT') re
     JOIN sys.objects o ON re.referenced_id = o.object_id;

SELECT DISTINCT
       CONCAT(re.referencing_schema_name,'.', re.referencing_entity_name) AS [name],
       o.type_desc AS [Type]
FROM sys.dm_sql_referencing_entities(QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@ObjectName) ,'OBJECT') re
     JOIN sys.objects o ON re.referencing_id = o.object_id;
Thom A
  • 88,727
  • 11
  • 45
  • 75