Solved

Table created in Stored Procedure

Posted on 2014-10-24
7
8 Views
Last Modified: 2016-06-15
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
Comment
Question by:AXISHK
7 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 40402453
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40402633
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40402796
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40403048
That doesn't actually tell you that proc did create that table, just that it potentially could have created it.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40403054
Granted.  Didn't have anything else to offer up, as table properties / sys.tables / sys.objects does not speak to it.
0
 

Author Comment

by:AXISHK
ID: 40405660
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question