Parse SQL statement for list of tables

Hello,
I would like to make a procedure of sorts that accepts as input a full
sql statment and then is able to return a list (or print) of only the tables
referenced in the sql statement. Is this kind of code available?
Thanks.
Bentweak this
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO
Create table #tempTables (SplitString varchar(50))
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='select * from authors join publishers on bla bla
bla...'
insert into #tempTables
SELECT SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID + 1,
CHARINDEX(' ', ' ' + @.chvGroupNumbers + ' ', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(' ' + @.chvGroupNumbers + ' ') - 1
AND SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID, 1) = ' '
GO
select * from #tempTables where Splitstring in (SELECT table_name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE')
Use Pubs for this example
http://sqlservercode.blogspot.com/
"Ben" wrote:

> Hello,
> I would like to make a procedure of sorts that accepts as input a full
> sql statment and then is able to return a list (or print) of only the tabl
es
> referenced in the sql statement. Is this kind of code available?
> Thanks.
> Ben

Parse dataset in report

I am using a "jump to report" that accepts multivalue parameters for fiscal periods. It works fine, returning the correct data. However, I want to add the chosen fiscal periods to the report header in a textbox. When I use

=Join(Parameters!DATEfiscalperiod.Value, ", ")

the results in the textbox show as:

[DATE].[fiscal_period].&[5], [DATE].[fiscal_period].&

When using::

=Parameters!DATEfiscalperiod.Value

results in

[rsInvalidExpressionDataType] The Value expression used in textbox ‘textbox5’ returned a data type that is not valid.

Is there a way to parse the dataset to only return "5, 6" in a string?

TIA

Hi, Takuma,

Try using the parameter label field instead:

=Join(Parameters!DATEfiscalperiod.Label, ", ")

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you Mary,

That works.

However, I also have a single value parameter. When I try to use this:

=Parameters!SALESPERSON.Label

no data is returned.

When I use:

=Parameters!SALESPERSONBDM.Value, I get this error:

[rsInvalidExpressionDataType] The Value expression used in textbox ‘textbox9’ returned a data type that is not valid.

Thank you for your help.

|||Is it possible to do this string concatenation in the sql that defines the dataset?
select field1 + ", " + field2 as fieldname
from ...
where ...

otherwise you can do similar things in the expression editor.

Parameters!Report_Parameter_0.Value.ToString() + ", " + Parameters!Report_Parameter_1.Value.ToString()|||

Thanks for the reply killerless,

Unfortunately, when I use

Parameters!Report_Parameter_0.Value.ToString()

it results in the whole dimension hierarchy structure: [SALESMGR].[]SALESPERSON.[NORTH]

I only want to show "NORTH".

Parameters!Report_Parameter_0.Value results in the same [SALESMGR].[SALESPERSON].[NORTH]

Parameters!Report_Parameter_0.:Label returns nothing.

It's also only a single value parameter.

Maybe I need to modify my mdx somehow but as you can see I'm a newbie.

|||So you have a parameter that is

[SALESMGR].[]SALESPERSON.[NORTH]?

|||

The parameter name that is passed is "salesperson".

I pass the parameter using "Jump to report". The parameter itself works fine, the data returned is correct. The only problem is displaying the chosen parameter in a user friendly format, (not in the mdx structure format).

The multivalue fiscal periods parameter works using the join().

|||Using the left or right functions you could reasonably easily filter out the unecessary text that is coming through to that textbox|||

Thank you for the help. That works.