AXISHK
asked on
Table created in Stored Procedure
I have a table that is created by Stored procedure. Is there a way quick to use query to identify which procedure generating this table ?
Tks
Tks
You mean from outside that proc? No, not unless you store that info somewhere.
If you mean can the proc "identity itself" as it's running, then yes, using @@PROCID.
If you mean can the proc "identity itself" as it's running, then yes, using @@PROCID.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That doesn't actually tell you that proc did create that table, just that it potentially could have created it.
Granted. Didn't have anything else to offer up, as table properties / sys.tables / sys.objects does not speak to it.
ASKER
Tks. It works fine only when I use a database that contains the store procedure. Can it be further enhancement to search through all database ? Tks
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%x%'
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%x%'
select * from sys.syscomments where text like '%PutYourTableNameHere%';