Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

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 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 (Bishop) 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.


      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, from sysdepends sd inner join sysobjects so on sd.depid = inner join @procs procs on = 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( as [ProcName], object_name(sd.depid) as [TableName] from sysdepends sd inner join @procs procs on = where resultObj = 1


Log In?

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (7)
As of 2015-03-30 09:48 GMT
Find Nodes?
    Voting Booth?

    When putting a smiley right before a closing parenthesis, do you:

    Results (635 votes), past polls