August 4, 2015 at 1:11 am
HI all,
Anyone has a script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists
My script is only for finding a value in one database
Many thanks guys
Cheers
August 4, 2015 at 7:32 am
WhiteLotus (8/4/2015)
HI all,Anyone has a script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists
My script is only for finding a value in one database
Many thanks guys
Cheers
I hope this is only for some sort of analysis or research as searching every column of every row of every table of every database is going take forever. If you have a script already to find a value in a database you just need to run that script for every database. Not sure what your current script so can't really help you extend it. If you post it maybe we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 4, 2015 at 8:20 am
WhiteLotus (8/4/2015)
HI all,Anyone has a script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists
My script is only for finding a value in one database
Many thanks guys
Cheers
Plenty of options for extending a script to search all databases. It would just be a matter of changing your script so it will loop through each of the databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2015 at 6:52 pm
Hi guys ...
Thanks for your kind reply ..Appreciate it
My script is as below : ( I have no idea how to modify this so I can search in all databases )
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
August 5, 2015 at 7:36 pm
Hi Guys ...
Can you please help me with this case ?
how do i find value in all tables and in all databases ..
Many thankss
August 6, 2015 at 6:36 am
About the simplest method would be to do this:
sys.sp_MSforeachdb @command1 = N'use ?',
@command2 = N'EXECUTE [sourcedatabase].[sourceschema].SearchAllTables'
Or write a cursor that will call the execution of your stored procedure.
Or do you want to rewrite the internals of the procedure?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 6, 2015 at 7:43 pm
Thanks for the reply . I wonder how to combine that statements into my current script . I want to rewrite the current script but have no idea how to do so...
Please kindly help if you can ...
Many thankss
August 7, 2015 at 7:24 am
WhiteLotus (8/6/2015)
Thanks for the reply . I wonder how to combine that statements into my current script . I want to rewrite the current script but have no idea how to do so...Please kindly help if you can ...
Many thankss
Why not just keep the existing code and then execute the code that Jason posted. I don't see any benefit of rewriting your code to find a value in all databases. Surely you aren't planning on running that on a normal basis? The performance is going to horrendous. And I mean REALLY BAD!!!! As in it could easily take a half day or longer even on a relatively small server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 8:45 am
Sean Lange (8/7/2015)
WhiteLotus (8/6/2015)
Thanks for the reply . I wonder how to combine that statements into my current script . I want to rewrite the current script but have no idea how to do so...Please kindly help if you can ...
Many thankss
Why not just keep the existing code and then execute the code that Jason posted. I don't see any benefit of rewriting your code to find a value in all databases. Surely you aren't planning on running that on a normal basis? The performance is going to horrendous. And I mean REALLY BAD!!!! As in it could easily take a half day or longer even on a relatively small server.
Agreed. Just scanning a single database for a value can take a half day or longer.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply