List Procedure That Creates A Table

Question

Post reply

List Procedure That Creates A Table

RonMexico

Hall of Fame

Points: 3474
More actions
January 23, 2024 at 2:32 pm

#4366436

I've been trying to develop a process that will take the input of a table name and trace that back to a stored procedure that creates, updates, or inserts into it. I've been piecing together queries using the sys.sql_dependencies, sys.sql_expression_dependencies, dbo.sysobjects, and dbo.syscomments tables but I'm not able to get the results that I'm looking for. I found that dbatools has the command Get-DbaDependency which will list any object dependent on a table which is similar so I'm hoping what I'm trying to do might already exist. Is anyone familiar with a method to do this?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Have you tried the stored procedure "sp_depends"? Not always giving the correct results, but it is one option.

Pinal Dave has a good post on this:

https://blog.sqlauthority.com/2010/02/04/sql-server-get-the-list-of-object-dependencies-sp_depends-and-information_schema-routines-and-sys-dm_sql_referencing_entities/

The method he recommends is to do something like this:

SELECT referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');

replacing the "dbo.First" with the object you are curious about the dependencies on.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

RonMexico Hall of Fame Points: 3474 More actions · Answer 2

I did come across Pinal Dave's solution but that is different than what I'm trying to do. The solution he lists returns any object dependent on the table whereas I'm trying to tie a table to the procedure that is responsible for updating it. That updating procedure will show up with his query but so will any other procedure that references the table.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

You could just search the "definition" column of sys.sql_modules for the table name with the understanding that it won't find ad hoc queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonMexico Hall of Fame Points: 3474 More actions · Answer 4

I also put something like that together where it would look like this

where ([definition] like '%INTO [tableName]%' or ([definition] like '%update [tableName]%')

That got me the closest I've been but I was worried about the accuracy of it.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

RonMexico wrote:

I also put something like that together where it would look like this
where ([definition] like '%INTO [tableName]%' or ([definition] like '%update [tableName]%')
That got me the closest I've been but I was worried about the accuracy of it.

I would just look for the table name and return the first 50 or so characters before it, as well. That way, you don't have to worry about spaces between INTO/UPDATE and the table name during the search.

And, again... it's not going to find any ad hoc code that's being executed from an app.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RonMexico Hall of Fame Points: 3474 More actions · Answer 6

I get the reasoning for taking the first 50 characters before the table name but I'm not sure it would accomplish what I want. If I use a charindex in the definition field for the table name then what happens if the table name is also in the procedure name or if the first time the name appears all it is doing is a simple select from that table? The query will recognize the table is referenced in a procedure but all I might be returned is something like "create procedure procHappensToHave[tableName]InIt" and it would miss anything about updating that particular table. Am I thinking about that the wrong way?

DesNorton SSC-Insane Points: 24399 More actions · Answer 7

RonMexico wrote:

I also put something like that together where it would look like this
where ([definition] like '%INTO [tableName]%' or ([definition] like '%update [tableName]%')
That got me the closest I've been but I was worried about the accuracy of it.

This may miss some instances.

Right off the top of my head, I have seen at least 4 variations of this

INSERT INTO tablename ...

INSERT INTO

tablename ...

INSERT tablename ...

INSERT

tablename ...

How to post data/code on a forum to get the best help.

RonMexico Hall of Fame Points: 3474 More actions · Answer 8

The code provided was just for explanation purposes. The real one I was using accounted for about ten different variations although I don't think that is still the best way which is what, I think, Jeff was trying to explain with getting 50 or so characters prior to the table name

Michael L John One Orange Chip Points: 27060 More actions · Answer 9

DECLARE @StringToFind varchar(256) /* String to find */
 DECLARE @ObjectType char(2) /* Only look for objects of this type */
 DECLARE @NumberToExtract int /* Number of characters to extract before and after the string to find */

SET @StringToFind = '@@Identity'
SET @NumberToExtract = 50 


 SELECT t.SchemaName, t.ObjectName, t.TypeDescription, t.CreationDate, t.ModificationDate,
  SUBSTRING
 (
 t.ObjectDefinition,
 CHARINDEX(@StringToFind, t.ObjectDefinition) - @NumberToExtract,
 LEN(@StringToFind) + (@NumberToExtract*50)
 )  AS Extract
 --INTO #t
 FROM
 (
 SELECT 
 S.name as SchemaName, 
 o.name AS ObjectName, 
 o.type_desc AS TypeDescription, 
 o.create_date AS CreationDate, o.modify_date AS ModificationDate,
 OBJECT_DEFINITION(object_id) AS ObjectDefinition
 FROM sys.objects o
 INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
 WHERE 
 ((o.type IN ('AF', 'FN', 'IF', 'P', 'TF', 'TT', 'U', 'V', 'X', 'TR') AND @ObjectType IS NULL) OR o.type = @ObjectType)
 AND REPLACE(REPLACE(OBJECT_DEFINITION(o.object_id), '[', ''), ']', '') LIKE '%' + @StringToFind + '%'
 ) AS t
 ORDER BY t.SchemaName, t.ObjectName, t.TypeDescription

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/