sp_msforeachtable

Question

sp_msforeachtable

ramadesai108

SSChampion

Points: 10609
More actions
December 22, 2010 at 3:04 pm

#144038

I need to find out which column has the value of "12/6" in a database. So how do i use sp_msforeachtable?
Thanks.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Do you know the tablename? If not, then you might need to make use of sys.columns and sys.tables information for constructing the query.

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

ramadesai108

SSChampion

Points: 10609

December 23, 2010 at 8:13 am

#1265947

Thanks for your response. Exactly how would I do it?

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I used this when looking for a phone number. I already knew which column to look in. If you don't know which column might have it, then I guess you have to check all the columns

exec sp_msforeachtable 'Select ''[?]'' as Table_Name, * from ?

where phone in (''2035551212'',''9145551212'')'

ramadesai108 SSChampion Points: 10609 More actions · Answer 4

ramadesai108

SSChampion

Points: 10609

December 27, 2010 at 6:33 am

#1266424

Thank you.

nekonecny Right there with Babe Points: 720 More actions · Answer 5

--change '%search string%' only

declare @num int, @count int, @exec nvarchar(500), @find nvarchar(50)

set @find =

declare @tab as table (TABLE_SCHEMA sysname,TABLE_NAME sysname,COLUMN_NAME sysname,Row_Number int primary key)

if (select OBJECT_ID('tempdb..#tab2')) is not null drop table #tab2

create table #tab2 (table_name sysname, colum_name sysname, column_value nvarchar(500))

insert into @tab

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME ,ROW_NUMBER() OVER(ORDER BY TABLE_NAME DESC) AS 'Row_Number'

from INFORMATION_SCHEMA.COLUMNS

where DATA_TYPE in ('varchar','nvarchar','char','nchar')

select @num=COUNT(*) from @tab

set @count = 1

while @count<=@num

begin

select @exec='set nocount on;INSERT INTO #tab2 SELECT ''' + TABLE_NAME + ''' as tab,''' + COLUMN_NAME + ''',['

+ COLUMN_NAME + '] FROM [' + TABLE_SCHEMA

+ '].[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] LIKE ''' + @find + '''' FROM @tab WHERE Row_Number=@count

--print @exec

execute (@exec)

set @count = @count +1

end

select * from #tab2

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

That is for only one database, but what if i want to find the search string on all databases on a particuar instance?

Thanks.

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

homebrew01

SSC Guru

Points: 55548

December 28, 2010 at 7:16 pm

#1266988

sp_msforeachdb

ramadesai108 SSChampion Points: 10609 More actions · Answer 8

But how would you use sp_msforeachdb with Niconecy's code?

domainsource SSC Rookie Points: 43 More actions · Answer 9

I have used the procedure at the following site worked for me same instance you need.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

ramadesai108 SSChampion Points: 10609 More actions · Answer 10

ramadesai108

SSChampion

Points: 10609

January 4, 2011 at 11:35 am

#1269140

That helped! Thanks.

amitchaudhury SSC Rookie Points: 40 More actions · Answer 11

how can use to find records after a certain date/time if knew the column name...

Thanks

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

amitchaudhury (8/23/2011)
how can use to find records after a certain date/time if knew the column name...
Thanks

Can you be more specific about the details of what you want to do ??

barsuk SSChampion Points: 12280 More actions · Answer 13

Maybe that will get you going:

if (select OBJECT_ID('tempdb..#tab')) is not null drop table #tab

Create table #tab (TABLE_SCHEMA sysname,TABLE_NAME sysname,COLUMN_NAME sysname,Row_Number int primary key)

if (select OBJECT_ID('tempdb..#tab2')) is not null drop table #tab2

create table #tab2 (table_name sysname, colum_name sysname, column_value nvarchar(500))

EXEC master..sp_MSForeachdb '

USE ?

IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

BEGIN

Select ''?''

declare @num int, @count int, @exec nvarchar(500), @find nvarchar(50)

set @find = ''%Search%''

insert into #tab

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME ,ROW_NUMBER() OVER(ORDER BY TABLE_NAME DESC) AS ''Row_Number''

from ?.INFORMATION_SCHEMA.COLUMNS

where DATA_TYPE in (''varchar'',''nvarchar'',''char'',''nchar'')

select @num=COUNT(*) from #tab

set @count = 1

while @count<=@num

begin

select @exec=''INSERT INTO #tab2 SELECT '''''' + TABLE_NAME + '''''' as tab,'''''' + COLUMN_NAME + '''''',['' + COLUMN_NAME + '']

FROM ?.['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + ''] WHERE ['' + COLUMN_NAME + ''] LIKE '''''' + @find + '''''''' FROM #tab WHERE Row_Number = @count

--print @exec

execute (@exec)

set @count = @count +1

end

select ''?'', * from #tab2;

END

'

DROP TABLE #tab

DROP TABLE #tab2

nigel. SSChampion Points: 11636 More actions · Answer 14

amitchaudhury (8/23/2011)
how can use to find records after a certain date/time if knew the column name...

Do you mean records inserted/updated after a specific date/time?

If so, unless you maintain a complete audit trail or a column/columns in all your tables that record when the record was inserted/updated, you are probably out of luck.

If you do have, for example, a 'datetime_inserted' column in your tables then it's simply a matter of something like:

WHERE datetime_inserted >= '20100101'

Nigel
How to post questions