Re: How to genericaly search the entire db for any text string in any stored procedure, function, table definition, agent job, etc. Any one command or anyone have a stored proc that does this wide search?
- From: "JWilliamsOH@xxxxxxxxx" <JWilliamsOH@xxxxxxxxx>
- Date: Fri, 7 Dec 2007 16:04:08 -0800 (PST)
On Dec 5, 10:36 pm, "DR" <softwareengineer98...@xxxxxxxxx> wrote:
How to genericaly search the entire db for any text string in any stored
procedure, function, table definition, agent job, etc. Any one command or
anyone have a stored proc that does this wide search?
Hmmm... You really do NOT want to do that, this will be a big load and
take a while; assuming you have a sizable DB. That said here you go,
give it a try;
This script will quickly generate DML to search all the tables in the
current database for a given string. It could be modified to execute
the generated code if desired. If you are running this in a <2005
environment, convert the VARCHAR(MAX)s to VARCHAR(8000) and be careful
of overflowing the variable if you have very wide tables. To use this
script, simply set the value of @SearchItem to whatever string it is
you are searching for on line 2 then run in the database you wish to
search.
DECLARE @searchSQL AS VARCHAR(MAX)
DECLARE @SearchItem AS VARCHAR(MAX) ; SET @SearchItem = '%YOURSTRING%'
SELECT @searchSQL = COALESCE(@searchSQL + '+ CAST(COALESCE(MIN(CASE
WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(MAX)) +
' THEN '' OR '' + IC.Column_Name + '' LIKE ''''' +
@SearchItem + ''''''' END), '''') AS NVARCHAR(max))' +
CHAR(13), 'CAST(COALESCE(MIN(CASE WHEN IC.Ordinal_position = ' +
CAST(Ordinal_position AS VARCHAR(MAX)) +
' THEN IC.Column_Name + '' LIKE ''''' + @SearchItem +
''''''' END), '''') AS NVARCHAR(max))' + CHAR(13))
FROM INFORMATION_SCHEMA.Columns
GROUP BY Ordinal_Position
ORDER BY Ordinal_Position
EXEC('SELECT REPLACE(Query, ''WHERE OR'', ''WHERE'') FROM
(SELECT ''SELECT '''''' + CAST(TABLE_NAME AS VARCHAR(MAX)) + '''''' as
tbl, * FROM ['' +
CAST(TABLE_SCHEMA AS VARCHAR(MAX)) + ''].'' + CAST(TABLE_NAME AS
VARCHAR(MAX)) + '' WHERE '' + ' + @searchSQL + ' AS query
FROM INFORMATION_SCHEMA.Columns IC
WHERE DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
GROUP BY TABLE_NAME, TABLE_SCHEMA) S')
.
- Follow-Ups:
- Prev by Date: Re: Query execution takes different times from different clients
- Next by Date: Re: MS Access to SQL 2000
- Previous by thread: Query execution takes different times from different clients
- Next by thread: Re: How to genericaly search the entire db for any text string in any stored procedure, function, table definition, agent job, etc. Any one command or anyone have a stored proc that does this wide search?
- Index(es):
Relevant Pages
|
Loading
