January 27, 2011 at 7:59 am
I have the wonderful SearchAllTables proc written by Narayana Kondreddi. The problem I am running into is that I need to search across all of the databases on a given SQL Server instance. This would require me to create the proc on each database one at a time, then fire it off on each database one at a time. I thought I could be slick and run it through the local server group query but this just runs against the master database, not each of the databases found under it.
I then thought I would be slick and write a little loop using sys.databases to create the proc on each database... but it won't let me use "Use [@dbname]". It also didn't like me using "not in ('master','tempdb','model','msdb')" in my original where statement for my loop.
So my question is this... how to I do a search for a value in a table that may exist on any one of 30-50 databases without checking one database at a time?
January 27, 2011 at 9:08 am
Name the SearchAllTables proc sp_SearchAllTables, load it into the master database and then run
exec sp_msforeachdb 'use ? exec sp_SearchAllTables ''searchstringhere'''
That should run it against each of the databases on your server without having to load the proc into more than just master.
January 27, 2011 at 9:45 am
Hmmm... I tried that and it seems to be trying to do something at least. I am getting back a results window full of empty rows.
Of note:
I counted the number of results rows... and it equals the number of databases... so that part seems to be working.
Thanks for starting me down the right track by the way.
This is the proc I am creating in master, could there be something in the proc that SQL 2008 doesn't recognize?
[font="Courier New"]CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (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'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
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)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(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[/font]
January 27, 2011 at 10:29 am
I see what you mean. The problem here is not how you are calling the proc (it is most assuredly running against every database), the problem is that there is a flaw in the logic of the proc, such that it is not always going to run against every table within each database. I added a simple print statement just before the Insert Exec and I see that it is only running the query against a few tables in certain circumstances.
The proc will have to be rewritten to absolutely guarantee that it will run against every table.
Bear in mind that once that is done, on a large server this WILL take a very long time to run.
January 27, 2011 at 10:34 am
Thank you SO much. I will indeed parse through the proc and see if I can make any changes to ensure that it hits every table. And yes... I realize that this will take an ungodly amount of time to run on some of our servers. There is evidently some kind of litigation (that I of course am not privvy to) that requires us to search everywhere for specific pieces of information that may or may not exist.
Once again, thank you.
January 27, 2011 at 12:13 pm
It occurs to me that your issue may be as simple as just making your proc a system object (so that it can run under any context without problem).
You should just have to run
use master
EXEC sp_ms_marksystemobject 'sp_SearchAllTables'
And then rerun
sp_msforeachdb 'use ? print ''?'' exec sp_SearchAllTables ''yoursearchstringhere'''
The proc itself is just fine, it just needs to be marked as a system object in order to work correctly in this context. Sorry for misleading you the first time here.
January 27, 2011 at 12:14 pm
Note, that when you do this you do have to prefix the proc with "sp_". Here is a good article to describe that.
January 27, 2011 at 12:38 pm
Huzzah!!! I am so excited. It is running and shows no signs of stopping anytime soon. I can only assume that this means it is actually out there searching and doing its thing. Thank you so much!:w00t:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply