Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

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.

Replies are listed 'Best First'.
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 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


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.

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 imbibing at the Monastery: (8)
As of 2016-10-23 15:31 GMT
Find Nodes?
    Voting Booth?
    How many different varieties (color, size, etc) of socks do you have in your sock drawer?

    Results (301 votes). Check out past polls.