Reverse Engineer SQL Scrpts

Question

Reverse Engineer SQL Scrpts

stevet 20325

SSC Veteran

Points: 275
More actions
September 15, 2011 at 9:00 am

#257529

I have a bunch of fairly complex SQL Scripts, mostly stored procs. I need to model out the tables and columns these things read. Is there a tool or an easier way that prowling through all that SQL to extract the Table/Column DDL?

Viewing 6 posts - 1 through 5 (of 5 total)

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

FibRock Right there with Babe Points: 726 More actions · Answer 1

Depending on what kind of information you need sp_depends presents a good amount of information on what is being used in a procedure and to some degree how it is being used. This is being phased out so you may want to look into sys.dm_sql_referencing_entities or sys.dm_sql_referenced_entities DMVs

Steve Fibich

http://stevefibich.net

Steve
http://stevefibich.net
http://utilitydb.codeplex.com/

Lowell SSC Guru Points: 323518 More actions · Answer 2

note that those two new DM views are in SQL 2008R2 and above:

sys.dm_sql_referencing_entities

sys.dm_sql_referenced_entities

i went to test in 2008 and saw they were not there...

you might have to stick witht hese two views in 2005/08:

select * from sys.sysdepends

select * from sys.sql_expression_dependencies

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 3

Are you looking to duplicate the affected tables? If you wrapped it in a stored proc, I believe the Red Gate Dependency Tracker would tell you which tables, and then you could script them.

Disclosure: I work for Red Gate.

stevet 20325 SSC Veteran Points: 275 More actions · Answer 4

I am trying to identify and duplicate the tables. there are many tables in each proc, and I just need to find them all and the columns that are being used. I'm working from offline scripts that create the stored procs right now, but may get access to the procs themselves hopefully tomorrow.

Steve Malley Ten Centuries Points: 1204 More actions · Answer 5

Codeplex has an ExportSQLScript.exe that I took a look at last week. It did not run 'as is' on my Windows 2008 R2 / SQL 2008 R2 configuration.

http://exportsqlscript.codeplex.com/