Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Modules for T-SQL summary statistics

by Win (Novice)
on May 12, 2006 at 13:49 UTC ( #548983=perlquestion: print w/ replies, xml ) Need Help??
Win has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am trying to summarise some MS SQL SPROC T-SQL .sql files. Does any one know of plans to develop a Perl module that could go though the T-SQL code and produce a summary for me? I want to summarise the following:

All tables created
All tables altered
All tables used


I could really do with a module like that now.

Comment on Modules for T-SQL summary statistics
Re: Modules for T-SQL summary statistics
by bart (Canon) on May 12, 2006 at 13:58 UTC
    It sounds like something you could produce with SQL::Parser, SQL::Statement, or similar modules. I'm not sure they can handle the T-SQL dialect, though.

    Also, there's an article on Perl.com on lexing SQL statements, Lexing your data, which is something simpler than parsing... but, it might be enough.

Re: Modules for T-SQL summary statistics
by marto (Chancellor) on May 12, 2006 at 14:09 UTC
Re: Modules for T-SQL summary statistics
by jplindstrom (Monsignor) on May 12, 2006 at 14:53 UTC
    Uhm... it sounds a bit strange to me. What do the stored procedures actually do in your case? Do they really create and alter a lot of tables? What do you mean by a "used" table?

    I have written a little something that graphs SP calls and inserts, updates, selects etc. in SPs. If that is what you're after, I can see if it's in a releasable state.

    /J

      Yep they create and alter a lot of tables. A used table is a table that is referred to in the FROM bit of an SQL statement.

      If you could look at that code it would be much appreciated.
Re: Modules for T-SQL summary statistics
by EdwardG (Vicar) on May 12, 2006 at 15:01 UTC

    Parsing T-SQL is hard, I know because I've tried.

    For tables created and altered you can get away with simple regex/grep, but for tables used I would forget parsing and use the sysdepends table.

    Here's part of the code I use to generate a tags file from several thousand T-SQL creation scripts

    sub gettags { my $filename=shift; return unless (-e $filename); my $objlinenumber; my $obj='(alter|create)\s+(procedure|proc|function|view)\s+([\.\_\ +w]+)'; ## Not doing variables, useless across multiple files #my $var='^(declare|[\s\,])*\@(\w+)\s+[\(\)\w\d\_]+'; my (%obj,@ret); my $line=0; open O, "<$filename" or die $!; while (<O>) { $line++; chomp; if (/$obj/i) { $obj{$3}=$line; } # proc | func | view if (/create\s+rule\s+([\.\_\w]+)/i) {$obj{$1}=$line;} # rule if (/sp_addtype\s+([\.\_\w]+)/i) {$obj{$1}=$line;} # UDT if (/create\s+default\s+([\.\_\w]+)/i) {$obj{$1}=$line;} # def +ault if (/create\s+table\s+([\.\_\w]+)/i) {$obj{$1}=$line;} # table } close O; foreach $obj (keys %obj) { $objlinenumber=$obj{$obj}; push @ret,"$obj\t$filename\t$objlinenumber\n"; } @ret; }

    And here's some T-SQL code to show what tables are 'used' (updated) by a given stored proc.

    -- --- What tables does the given procedure(s) update? -- declare @Procs table ( id int , depid int ) insert into @Procs (id) values (object_id('my_table')) -- add rows here -- add rows here -- de-nest these procs while 1 = 1 begin insert into @Procs (id,depid) select distinct sd.depid, sd.id from sysdepends sd inner join sysobjects so on sd.depid = so.id inner join @procs procs on procs.id = sd.id where sd.depid not in (Select id from @Procs) and (so.xtype <> 'u') if @@rowcount > 0 break end -- get a list of updated tables select distinct object_name(sd.id) as [ProcName], object_name(sd.depid) as [TableName] from sysdepends sd inner join @procs procs on procs.id = sd.id where resultObj = 1

     

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://548983]
Approved by moklevat
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2014-10-21 03:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (95 votes), past polls