Wednesday, March 28, 2012

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

No comments:

Post a Comment