Extract table name from SQL

				
=**************************************
= Name: Extract table name from SQL
= Description:Extract table name(s) from an SQL statement.
= By: John Winger
=
= Inputs:The SQL query.
=
= Returns:The table name or names. This routine can be called in scalar or list context.
=
= Assumes:The script I am providing is a testing environment for the routine that you can run from the command line on your system. You can just pull out the routine and plug it in wherever you might need it.
=
= Side Effects:If you call the routine in scalar context and there is more than one table name then you will only get the first table name (i.e. the data will be truncated).
=
=This code is copyrighted and has= limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=323&lngWId=6=for details.=**************************************

##
## Writen by John Winger (john@wingeronline.com)
##
## Use as you wish, but as a courtesy I ask that you give me credit in your code.
##
print "\nTesting routine that extracts the table name from SQL\n\n";
print "Enter SQL (hit 'Enter' or 'Return' to quit): ";
$sql = <STDIN>;
chomp($sql);
while ($sql)
{
	@tables = extrTblNameFromSql($sql);
	print "Table: @tables\n\n";
	
	print "Enter SQL: ";
	$sql = <STDIN>;
	chomp($sql);
}
sub extrTblNameFromSql
{
	my $sql = shift;
	
	return "" if !$sql;
	
	my $sql_lc = lc($sql);
	my @tablename = ();
	my $tbl_sub = 0;
	my $total_len = length($sql);
	my $cur_loc = 0;
	
	if ($sql =~ m/^INSERT INTO /i)
	{
		# working with an "insert" statement
		$cur_loc = 12;# position of the first character in the table name
	}
	elsif ($sql =~ m/^UPDATE /i)
	{
		# working with an "update" statement
		$cur_loc = 7;# position of the first character in the table name
	}
	elsif ($sql =~ m/ FROM /i)
	{
		# working with a "select" or "delete" statement
		my $from_loc = index($sql_lc, " from ");# location of 'FROM' keyword
		$cur_loc = $from_loc + 6;# position of the first character in the table name
	}
	else
	{
		return;# error, not a valid sql statement
	}
	
	my $c = substr($sql, $cur_loc, 1);# extract table name character by character
	until ($c eq " ")# || $c eq ",")
	{
		$tablename[$tbl_sub] .= $c;# build table name one character at a time
		
		$cur_loc++;
		last if ($cur_loc > $total_len - 1);# force exit from loop
		
		if (substr($sql, $cur_loc, 1) eq ",")
		{
			# this must be a select statement that involves more than one table
			$cur_loc++;
			until (substr($sql, $cur_loc, 1) ne " ") { $cur_loc++; }
			$c = substr($sql, $cur_loc, 1);
			$tbl_sub++;# next subscript in the tablename array
		}
		else
		{
			$c = substr($sql, $cur_loc, 1);
		}
	}
	
	return @tablename if (wantarray());# list context detected
	return $tablename[0] if (defined wantarray());# scalar context, could truncate data
}