| Author |
Topic |
|
spthomas
Starting Member
14 Posts |
 Posted - 2011-09-15 : 10:59:10
|
| 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? |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-15 : 11:13:29
|
Something like this perhaps? There are probably better thought through scripts around that do the same thing (better!)SELECT TOP 10 O.type_desc, O.name, D.* FROM sys.objects AS O JOIN sys.sql_expression_dependencies AS D ON D.referencing_id = O.object_idWHERE type IN ('FN', 'P', 'TF', 'TR', 'V') AND O.name LIKE '%MyObjectName%' |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-09-15 : 12:07:20
|
| There is no reason to think that you will be able to reverse engineer the table designs from SQL scripts and stored procedure code.Why not just look at the database, and script out the tables directly?CODO ERGO SUM |
|
|
|
spthomas
Starting Member
14 Posts |
Posted - 2011-09-16 : 17:24:33
|
| This just gets better. I'm wading through the stored procedure code, which uses a lot of temp tables and joins, and I'm starting to figure out the column level data mappings. Now, I need to find the source tables. But I don't know which SERVER they're on! Within the proc there are of course the FROM clauses, and I see a tyical example is: FROM AR..SPP32254 SPP32254 with (nolock)So I'm looking for the table SPP232254, owner not specified, on Database AR. I look on the "normal" server and there is no database AR! Is there anyway to know which server to look on? There are several of these things I have to backtrack. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-18 : 03:24:30
|
| Might be a VIEW?The "with (nolock)" would fill be with horror about the code, one of the most misused things I see, and usually with side effects which can cause dire consequences for the recipient of the data / company.EDIT: Sorry, misread "there is no database AR!" as you not finding the Table ... its the Database you are looking for.Dunno what aliases there are for Databases, as I don't use them - Synonyms? Snapshots? perhaps |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-18 : 03:28:45
|
| do you atleast know how many servers totally you've? if yes what you can do is to check sys.databases catalog view to check if that db is in server likeSELECT * FROM sys.databases WHERE name ='AR'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
spthomas
Starting Member
14 Posts |
Posted - 2011-09-18 : 08:59:54
|
| There are many servers. Is each server a different separate instance of SqlServer? So there is no connection, no catalog that spans servers? |
|
|
|
spthomas
Starting Member
14 Posts |
Posted - 2011-09-18 : 09:00:06
|
| There are many servers. Is each server a different separate instance of SqlServer? So there is no connection, no catalog that spans servers? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-18 : 09:02:13
|
quote:
Originally posted by spthomas
There are many servers. Is each server a different separate instance of SqlServer? So there is no connection, no catalog that spans servers?
then no other way than to manually check in each :(------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
spthomas
Starting Member
14 Posts |
Posted - 2011-09-23 : 18:55:49
|
| Let me try to be clearer and see if anyone knows of a way or a tool or anything.I'm tasked with designing a Data Mart that will take data from a lot of applications. It is replacing them as the source for about 100 reports plus a cube. I need to prowl through all those reports (most of which are created with stored procs), and "extract" the data definitions. So I have to read every stored proc through, finding all the FROM clauses and deciphering what the tables/columns it uses as source. I can do this, it's not complex. But it is very time consuming to read through all of these.It would seem there is a way, or even a tool, that parses through a SQL statement and just extracts out the data source information. If all I get out of this is a list of tables and columns that are used, it will be a lot easier to work with.Any ideas? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-24 : 07:16:10
|
| "and "extract" the data definitions"The results from the Sproc? if so use SELECT * INTO to put the results into #TempTables with OPENQUERY() or somesuch and examine the data types of the columns in the TEMP tablesIf you want the whole select statement, and where clause, from the original SProc then you will have to extract that from the source code - its presumably going to have @Parameters in the WHERE clause as criteria for the existing reports, so going to need some adjustment anyway (unless the SProcs were mechanically generated and just do some sort of SELECT without manipulating any @Parameters, and don't use any #TEMPs or @TempTables etc. |
|
|
|
spthomas
Starting Member
14 Posts |
Posted - 2011-09-26 : 11:02:27
|
| I'm not looking at the output, but the input. And the actual count of SProcs is about 150. And I have to have them all done in 3 days. So just looking for a shortcut to get to a standard Table/Column layout for each table and column that is used as input. So reading the procs wasn't the issue. Reading through 150 of them and finding all the data they use in about three days was the issue. But if I gotta, I gotta.I just thought there must be some sort of a DDL profiling tool that could do this drudge work. |
|
|
|
|
Reverse Enginner SQL Scripts