1
I have a stored-Procedure in SQL 'spr_insere_carga_requested' where when executing the sp_helptext command in a given database it was not identified; there is some way to identify the database where this stored Procedure was created?
1 answer
3
Yes it is possible.
Basically all objects in the database are in tables like sys.tables, sys.procedures and sys.objects, just select them, but these tables exist in each database, so you would need to run select on all Databases individually.
To solve this, there is a precedent that makes a foreach in all the Bases, which is the sp_MSforeachdb.
Then it’s simple, just make a select in the table sysobjects by searching for the name of the past, filtering through the field xtype='P', which is "Procedure" (the sysobjects has all the database objects such as Tables and functions) and in select add the db_name() to list the name of the bank.
Stay like this:
sp_MSforeachdb 'select db_name(), * From ?..sysobjects where xtype = ''P'' And name = ''spr_insere_carga_pedido'''
Browser other questions tagged sql sql-server stored-procedures
You are not signed in. Login or sign up in order to post.
It worked! Thank you very much!
– Paulo Iletski