Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

sp_msforeachtable Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 3:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
I need to find out which column has the value of "12/6" in a database. So how do i use sp_msforeachtable?

Thanks.
Post #1038522
Posted Wednesday, December 22, 2010 11:26 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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.
Post #1038612
Posted Thursday, December 23, 2010 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thanks for your response. Exactly how would I do it?
Post #1038802
Posted Thursday, December 23, 2010 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 2,827, Visits: 8,470
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'')'




Post #1038813
Posted Monday, December 27, 2010 6:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thank you.
Post #1039392
Posted Tuesday, December 28, 2010 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 17, 2014 8:16 AM
Points: 28, Visits: 443
--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
Post #1039844
Posted Tuesday, December 28, 2010 10:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
That is for only one database, but what if i want to find the search string on all databases on a particuar instance?

Thanks.
Post #1039877
Posted Tuesday, December 28, 2010 7:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 2,827, Visits: 8,470
sp_msforeachdb


Post #1040064
Posted Wednesday, December 29, 2010 6:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
But how would you use sp_msforeachdb with Niconecy's code?
Post #1040226
Posted Thursday, December 30, 2010 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:56 AM
Points: 3, Visits: 159
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
Post #1041011
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse