Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: Modules for T-SQL summary statistics

by EdwardG (Vicar)
on May 12, 2006 at 15:01 UTC ( #549023=note: print w/ replies, xml ) Need Help??


in reply to Modules for T-SQL summary statistics

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

 


Comment on Re: Modules for T-SQL summary statistics
Select or Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2014-10-26 01:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (149 votes), past polls