SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_msforeachtable


sp_msforeachtable

Author
Message
ramadesai108
ramadesai108
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 690
I need to find out which column has the value of "12/6" in a database. So how do i use sp_msforeachtable?

Thanks.
MANU-J.
MANU-J.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 8766
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
ramadesai108
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 690
Thanks for your response. Exactly how would I do it?
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4845 Visits: 9108
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
ramadesai108
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 690
Thank you.
nekonecny
nekonecny
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 493
--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
ramadesai108
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 690
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
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4845 Visits: 9108
sp_msforeachdb



ramadesai108
ramadesai108
SSC Eights!
SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)SSC Eights! (851 reputation)

Group: General Forum Members
Points: 851 Visits: 690
But how would you use sp_msforeachdb with Niconecy's code?
domainsource
domainsource
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 210
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search