sp_msforeachtable

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

    Thanks.

  • 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.

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

  • 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'')'

  • Thank you.

  • --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

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

    Thanks.

  • sp_msforeachdb

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

  • 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

  • That helped! Thanks.

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

    Thanks

  • 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 ??

  • 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

  • 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'

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

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