?
Solved

Table created in Stored Procedure

Posted on 2014-10-24
7
Medium Priority
?
12 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 40

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 66

Accepted Solution

by:
Jim Horn earned 2000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 66

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

801 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