Finding data

Sorry about posting this in multiple groups but no one in the clients group
seemed to know the answer.
Here's the question;
Is it possible to search an entire database for a string or number? If so
How?
- Hamilton
Hamilton,
You could develop your own stored procedure by iterating through the COLUNM_NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require a cursor. Any matches should also display the table name, owner and column name. A proc might exist on the we
b somewhere to do this, there is nothing native to SQL Server that will do this currently. Be aware that such a search could take a very, very long time to complete as you will be table scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per table, and not one table scan per column - that should keep the already bad performance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Thanks Mark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:D3376437-D5CE-441C-8A86-3948921C9CB6@.microsoft.com...
Hamilton,
You could develop your own stored procedure by iterating through the
COLUNM_NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require
a cursor. Any matches should also display the table name, owner and column
name. A proc might exist on the web somewhere to do this, there is nothing
native to SQL Server that will do this currently. Be aware that such a
search could take a very, very long time to complete as you will be table
scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per
table, and not one table scan per column - that should keep the already bad
performance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Finding data

Sorry about posting this in multiple groups but no one in the clients group
seemed to know the answer.
Here's the question;
Is it possible to search an entire database for a string or number? If so
How?
- HamiltonHamilton,
You could develop your own stored procedure by iterating through the COLUNM_
NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require a curs
or. Any matches should also display the table name, owner and column name. A
proc might exist on the we
b somewhere to do this, there is nothing native to SQL Server that will do t
his currently. Be aware that such a search could take a very, very long time
to complete as you will be table scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per t
able, and not one table scan per column - that should keep the already bad p
erformance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Thanks Mark
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:D3376437-D5CE-441C-8A86-3948921C9CB6@.microsoft.com...
Hamilton,
You could develop your own stored procedure by iterating through the
COLUNM_NAME of the system INFORMATION_SCHEMA.COLUMNS view. This will require
a cursor. Any matches should also display the table name, owner and column
name. A proc might exist on the web somewhere to do this, there is nothing
native to SQL Server that will do this currently. Be aware that such a
search could take a very, very long time to complete as you will be table
scanning every table in your database.
Try and design your proc so that you are are only doing one table scan per
table, and not one table scan per column - that should keep the already bad
performance to a minimum.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk

Filtering without a tuple-set ?

a normal Filter with a tuple-set:

Filter(
[Brand].members,
(([Measures].[Sale],
[Posting Period].[Year].&[2005]) > 0)
)

The brands where we sold something in the year 2005. This works fine but....

a Filter within a range of days ? :

Filter(
[Brand].members,
(([Measures].[Sale],
ClosingPeriod([Posting Period].[Day]).lag(365):ClosingPeriod([Posting Period].[Day]))) > 0)

The brands where we sold something in a range between currentday -365 days and currentday.
This won't work with the filter-function ! Is there an other way to do this ?

Kind regards

If your Sales measure does not contain negatives you could simply aggregate the measure for the range of dates.

Filter(
[Brand].members,
Aggregate(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ) > 0)

If your measure might contain negative figures and there is a chance that a date range could sum to 0 even though there are sales for the specified period, you might be better off getting a count of the non empty cells.

Filter(
[Brand].members,
NonEmpty(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ).Count > 0)