Reverse Engineer SQL Table

Question

Reverse Engineer SQL Table

Viewing 7 posts - 16 through 21 (of 21 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

To search within SSIS packages that are in the file system, I use Notepad++ with the "Find in Files" option (Ctrl+Shift+F). It'll find if the text is used in the package, after all, they're just XML files. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

SQL_Kills (8/5/2014)
1. If you look at the attachment , it is showing blank because when you deploy a ssis package to the server it will stored this to a file i think in the msdb so it wouldn't be stored in any other database?
2. I don't need this field but it will only have the value that you will be passing in as the parameter as that is the table you are searching
3. This is just field name I have picked, it basically tells you the name of of the stored proc, function it uses this table on
4. sourceType (just a name I have given as a fieldName change it if you want to) if you look at the attachment is telling me what this relates to so if the table is in a stored procedure it will say STORED PROCEDURE

Not trying to be difficult here but just to level the playing field and gather most of us in the same base, SourceName would be the sql object/external object name, the source type is the object type, including the type of the external object?

SQL Server holds no real registry of external objects, no correlation of SSIS package source / destination to any databases so that is a tough one. For any native SQL object, it should be pretty straight forward. the OBJECTPROPERTY will tell you everything you ever wanted to know about an SQL object but where too afr....

😎

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 3

Erikir, the ssis package search will work from the query I have provided, I have aslo provided one to search for stored procedures. If i was a new person starting at your company and I asked where is this table used and how is this table populated etc.. how would you go by it, what steps would you do to achieve this? Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The one you say is for procedures is actually for procedures, functions and views, but you will get false positives.

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

Lowell SSC Guru Points: 323518 More actions · Answer 5

yep it's possible, but it's not built in.

using the GUI or via SMO is infinitely easier; I know, because i poured weeks of effort down the TSQL-only method, and it's doesn't cover every possible nuance.

i made a script i contributed,and an article that gets a little bit of traffic as well; the current version scripts out any table, temp table, or procedure/function/view.

exec sp_GetDDLa TableName;

exec sp_GetDDLa #TempTable;

exec sp_GetDDLa ProcedureName;

you have to stick it in master and mark it as a system procedure, as it says in the instructions, in order to use it from any database.

see the thread for a lot of different versions from other people who took my version and adapted it to their own needs.

sp_GetDDLa_Latest.txt

it's like a thousand lines of code, and can still be improved when it comes to scripting the indexes details out.

the thread:

http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

and the four year old article from 2009:

Get DDL for any SQL 2005 table[/url][/quote]

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!

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 6

SQL_Kills

Hall of Fame

Points: 3154

August 5, 2014 at 3:31 pm

#1735405

Thanks i will have a look at your code

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

First of all, you misspelled my name:blush: (no worries, I'm just really hurt) Joking apart (but don't do it again :-P), the condition in the STORED PROCEDURE query (as Gail mentioned) is going to give you false positives. So would the XQuery if any package uses the object without populating it.

you might even be better off auditing something like

select h.text,h.objectid,h.dbid from sys.dm_exec_connections c

outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) h

😎