Running a system Stored Procedure of SQL Server using python pyodbc

Question

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?

FYI, sp_depends is deprecated anyway, so perhaps you should be looking at an alternative system object. See the documentation 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
– Thom A ♦, Commented Jan 11, 2022 at 11:57
Also, I strongly suggest you learn to parametrise; the above is wide open to SQL Injection attacks. — Thom A
– Thom A ♦, Commented Jan 11, 2022 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
– pythondumb, Commented Jan 11, 2022 at 12:07
I suggest a read of 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
– Thom A ♦, Commented Jan 11, 2022 at 12:07
"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
– Thom A ♦, Commented Jan 11, 2022 at 12:15

Thom A · Accepted Answer · 2022-01-11 12:54:24Z

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;

Collectives™ on Stack Overflow

Running a system Stored Procedure of SQL Server using python pyodbc

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related