Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13
  • Last Modified:

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
0
AXISHK
Asked:
AXISHK
1 Solution
 
lcohanDatabase AnalystCommented:
If you know the table name please try following select in SSMS against that specific dabatase (not master):

select * from sys.syscomments where text like '%PutYourTableNameHere%';
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If your question is 'What SP whose name I don't know creates table x?', then the below code will return all SP's with the value x in it.  If the set is too big, or if you feel lucky, change x to CREATE TABLE x
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
   AND definition LIKE '%x%'

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
That doesn't actually tell you that proc did create that table, just that it potentially could have created it.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Granted.  Didn't have anything else to offer up, as table properties / sys.tables / sys.objects does not speak to it.
0
 
AXISHKAuthor Commented:
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%'
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now