Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

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, 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: note [id://549023]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (12)
As of 2017-07-24 16:05 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (356 votes). Check out past polls.