How do I find the tables created by a t-sql stored procedure?

Question

How can I find out what tables a T-SQL stored procedure creates when it runs?

We have hundreds of stored procedures and I am looking for hints on how to go about this.

We can do most things on the SQL Server 2012 itself, or in C# (Framework 3.5).

Probably SQL Profiler is the most relevant way to find out what really happens. Have tried it already? — xacinay, Jun 06 '14 at 14:41
@xacinay Sadly, that's one of the few things we can't do on the server, as we don't have full administration privileges. — Christopher Mahan, Jun 06 '14 at 14:48
Maybe you can use this and look for tables created within a specific date range. http://stackoverflow.com/questions/1171019/sql-server-table-creation-date-query — Ola Ekdahl, Jun 06 '14 at 14:52
@OlaEkdahl Unfortunately we rebuild several hundred tables each morning, with multiple machines, running several jobs at the same time, so that wouldn't work. — Christopher Mahan, Jun 06 '14 at 15:16
@ChristoperMahan: Can you provide an example of what you are looking for? Regular expressions in a separate editor might help if you are able to extract them — Dominic Zukiewicz, Jun 06 '14 at 15:21

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

This StackOverflow post should help you, entitled 'How do I find a stored procedure containing text'.

It runs various commands to extract the stored procs and their associated text, when it just doing a LIKE on the content.

For reference, this is:

DECLARE @TextToSearchFor varchar(50) = 'CREATE TABLE'

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%' + @TextToSearchFor + '%' 
    AND ROUTINE_TYPE='PROCEDURE'

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%' + @TextToSearchFor + '%'  
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%' + @TextToSearchFor + '%'

edited May 23 '17 at 10:26

Community

1
1

answered Jun 06 '14 at 14:52

Dominic Zukiewicz

8,258
8
43
61

we went down that road. and we may have to go down it again. Most of what we have are select into though. – Christopher Mahan Jun 06 '14 at 15:09
`DECLARE @TextToSearchFor varchar(50) = 'SELECT INTO'` :) – Dominic Zukiewicz Jun 06 '14 at 15:13
Sadly, it's usually SELECT \n lots of stuff \n INTO – Christopher Mahan Jun 06 '14 at 15:15

How do I find the tables created by a t-sql stored procedure?

1 Answers1