November 2, 2015 at 12:34 pm
All:
I tried to find a forum for "tools" with SQL Server 2008, but couldn't find anything, so hoping this is the right forum for my question.
I have recently installed RedGate SQL Search. Although I can use this tool for a lot of stuff, I mis-understood the details and thought it not only searched database objects, but would search inside individual rows inside tables for a value, but it does not do this.
I have been trying to search for a comparable product. I have found a bunch of stored procedures to do this, but I do not want to create a bunch of stored procedures for all the various databases we have, nor do I have the permission to do this.
Are there any tools like SQL Search that integrate (not a requirement, but definitely a nice bonus) with SSMS and will search the tables for a value in any given row, listing the table and column (it would be nice to list the entire row, but not required as with table and column, I can do a search for the row) for the given value?
I have found this product: http://www.apexsql.com/sql_tools_search.aspx, but I do not have any information on the company to know if they are reputable. Would this be a good product, or do any of you know know of a better product?
Thanks....
November 3, 2015 at 1:38 am
Or you could try this aswell
November 3, 2015 at 3:39 am
Hello,
In the past I created a number of search routines.
This was quite a jumble in different styles.
Now I have bundled the search routines in a stored procedure and the master database holds this procedure, so it can be used in any database.
It is not a completely polised stored procedure, to publish it here it still needs some work and some collaboration. It does use some other stored procedures. Which are functional on their own.
Exec sp_searchall '%ben%'
Searches (almost) all columns for the appearance of ben in the string.
Exec sp_searchall '%yellow%|%blue%|%red%'
Searches with multiple values.
Selection on table_name and/or column_name is possible. (To speed the process up).
sp_searchall 'date', '2015-01-01','2016-01-01'
Exec sp_searchall 'int', 123456,223456
Searches for the values in the range on data/int's including start excluding end value.
During the search in an other window use:
sp_searchall Progress
And this shows the progress for the searches going on at that moment.
Exec sp_searchall show, 15
Shows 15 rows of each column searched and found with the value.
There are also meta data searches over the current database or over all databases.
exec sp_searchall 'FOREACH', '%Ben%'
-- Searches all databases, all table_names, all column_names, routine_names containing the string.
If some collaboration within this group is possible, I am prepared to adapt the procedure and publish it here.
If not I can extract and adjust the code for just the simple search with a single string.
Ben
Part of the output of the help function of the sp_searchall stored procedure.
-- Content Search actions
-- sp_searchall Help, Date -- Searches all dates for ranges
-- sp_searchall Help, Int -- Searches all ints for ranges
-- sp_searchall Help, Multi -- Searches multiple values
-- sp_searchall Help, Orphan -- Searches orphans
-- sp_searchall Help, Object -- Searches in object names and routine definitions
-- sp_searchall Help, Missing -- Searches 'missing' objects.
--
-- Meta data Search actions
-- sp_searchall Help, Meta -- Get help on Search actions on Object, routines, alle databases, tables, columsn
-- Other functions
-- sp_searchall Help, SHOW -- Shows previous result.
-- sp_searchall Help, SHOW20 -- Shows 20 ROWS of each previous result.
-- sp_searchall Help, Clear -- Clears previous results
-- sp_searchall Help, Statement -- Generates a statement for each row in the resultset
-- sp_searchall Help, progress -- Shows progress of searches in 'all' connections
--
-- sp_searchall Help, Parameters -- Help on parameters, parameters is not a function
November 3, 2015 at 6:25 am
anthony.green (11/3/2015)
Or you could try this aswell
The refereced site gives a compact search 'tool'.
I got some errors and have made some changes to the code.
Now: Searches are only done on tables not on views.
XML fields are excluded from the search. (Did not fit in the result).
Ben
-- Changes from the original.
-- -- Original :http://www.sqlservercentral.com/scripts/T-SQL/69207/
-- Search only in Base tables. (Prevents errors on Views and is more efficient).
-- Skip XML rows. (Prevents fields which are to large for the result table).
-- Changes by: Ben Brugman
-- 20151102
--initialize transaction
set transaction isolation level read uncommitted
set nocount on
--initial declarations
declare @rowID int, @maxRowID int
declare @sql nvarchar(4000)
declare @searchValue varchar(100)
declare @statements table (rowID int, SQL varchar(8000))
create table #results (tableName varchar(250), tableSchema varchar(250), columnName varchar(250))
set @rowID = 1
set @searchValue = 'test'
--create CTE table holding metadata
;with MyInfo (tableName, tableSchema, columnName) as (
select c.table_name, c.table_schema, c.column_name
from information_schema.columns C join information_schema.TABLES T on C.table_name = T.table_name and T.table_type = 'Base Table'
where c.data_type not in ('image','text','timestamp','binary','uniqueidentifier','xml')
)
--create search strings
insert into @statements
select row_number() over (order by tableName, columnName) as rowID, 'insert into #results select distinct '''+tableName+''', '''+tableSchema+''', '''+columnName+''' from ['+tableSchema+'].['+tableName+'] where convert(varchar,['+columnName+']) like ''%'+@searchValue+'%''' from myInfo
--initialize while components and process search strings
select @maxRowID = max(rowID) from @statements
while @rowID <= @maxRowID
begin
select @sql = sql from @statements where rowID = @rowID
exec sp_executeSQL @sql
set @rowID = @rowID + 1
end
--view results and cleanup
select * from #results
drop table #results
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply