Our database engineer builds stored procedures for us to call from Python/R etc. These now number in the multiple 10s, over 2-3 databases.
I've got records of what 70% of them do, but I wondered if there was a quick/dirty way to query the database(s), and pull back a list of available stored procedures (given the supplied logins within the connection string.)
I typically use
sql = "
EXEC stored_proc_name
@param1 = 'xyz',
@param2 = 'abc'
"
cnxn = pymssql.connect(
host = r'ip.ad.dr.es.',
port = '1433',
user = r'db_user',
password = r'pdws',
database = 'db_name'
)
and then the wonderful pd.read_sql_query(sql, cnxn). This works fine - as long as i know the stored proc name and params required.
I've got most of them hard coded in to a module, but just thought i'd see if anyone knows of some in-built functionality to interrogate the database for this info - at least the available stored procedures, but also the required params too, if at all possible.
Ok this became quickly irrelevant for the initial issue, but just this week became relevant again.
What I was looking for was:
sql_SPs = """Select [NAME] from sysobjects where type = 'P' and category = 0"""
and when called as:
df_1 = pd.read_sql_query(sql_SPs, cnxn)
Returns a dataframe (fine in this instance) with the stored procedures listed