Finding TableNames and Column Names in storedProcedure.

Question

Finding TableNames and Column Names in storedProcedure.

krisna

SSC Journeyman

Points: 76
More actions
February 21, 2012 at 2:56 pm

#257080

we have nearly 100 stored procedures so i need Find what tables and what columns were used in particular stored procedure.

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Try using the sys.dm_sql_referenced_entities DMV.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You could also use

SELECT text FROM sys.syscomments WHERE Text LIKE ('%dbo.sp_upgraddiagrams%')

Where '%dbo.sp_upgraddiagrams%' is your stored procedures name it return the text of the stored procedure which you can then examine.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

syscomments is deprecated, should not be used, only for backward compat with SQL 2000, use sys.sql_modules instead.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

GilaMonster (2/21/2012)
syscomments is deprecated, should not be used, only for backward compat with SQL 2000, use sys.sql_modules instead.

Gail thanks for pointing that out.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

SELECT definition FROM sys.sql_modules AS sm WHERE object_id = OBJECT_ID('sp_upgraddiagrams')

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krisna SSC Journeyman Points: 76 More actions · Answer 6

hi gila, thanks for reply, i didn't get the below result by using your code or i didn't use properly. can you give some more details to use because i new to this sql.

Table Name Procedure Name Field Name

tblorder p_process_jda_IN_custom_UPTOD_PN

OD_CATEGORY

OD_ENTRY_DATE

p_process_jda_IN_MASTER_UPTOD_PN

OD_CATEGORY

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Did you try the DMV I suggested?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yuri55 SSCrazy Eights Points: 8441 More actions · Answer 8

You should use “definition” column (WHERE definition like ‘%your_tbl_name%’) from sys.sql_modules system view as Gail suggested if you would like to find what SP refers to "your_tbl_name" table