Searching for a table

  • Hi,

    Recently one of my maintenance plans for reorganizing indexes has started to fail. The history log tells me what index it was trying to reorganize, and on what table, but not in which database. So I need a way of finding where this table is. I couldn't find a view that shows all tables for every db, but only all tables for the current db, so I tried using a cursor to iterate over all databases checking for this table. The code appears to work as expected, but it still can't find this missing table - can anyone help me get to the bottom of this?

    declare @db varchar(100)

    declare my_cursor cursor

    for select name from sys.databases order by name

    open my_cursor

    fetch next from my_cursor into @db

    while @@fetch_status = 0

    begin

    exec('use [' + @db + ']')

    if exists(select * from sys.tables

    where name = 'LatestObjectProperty')

    begin

    select 'Foud it!'

    select @db

    end

    fetch next from my_cursor into @db

    end

    close my_cursor

    deallocate my_cursor

    Many thanks,

    Bodsda

  • Use this :

    sp_MsForeachdb '

    Use ?

    select db_name(), * from sys.objects where name = ''your_object_name''

    '

    then you will see in which db youyr object is and what type it is :

    U = user table

    S = system table

    ...

    I bet this will be 'S' (sys.tables only returns User tables).

  • I prefer to avoid CURSORs and loops when they aren't necessary, including the hidden one in sp_MsForeachdb.

    So how about this?

    DECLARE @TABLE VARCHAR(200), @sql VARCHAR(MAX)

    --Add your table name here

    SET @TABLE = ''

    SELECT @sql = COALESCE(@SQL,'') +

    'USE ' + QUOTENAME(NAME) + CHAR(10) +

    'SELECT ''' + NAME + '''AS dbName, name, type, type_desc' + CHAR(10) +

    'FROM sys.objects' + CHAR(10) +

    'WHERE name = ''' + @TABLE + ''';'

    FROM sys.databases

    EXEC(@SQL)


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for that, it worked. But why didn't my cursor work?

    Thanks,

    Bodsda

  • bodsda (1/6/2012)


    Thanks for that, it worked. But why didn't my cursor work?

    Thanks,

    Bodsda

    Because your EXEC only exists in relation to itself, it doesn't affect anything outside of it.

    Try this, hopefully it'll explain: -

    DECLARE @db VARCHAR(100)

    DECLARE my_cursor CURSOR FOR SELECT NAME

    FROM sys.databases

    ORDER BY NAME

    OPEN my_cursor

    FETCH NEXT FROM my_cursor INTO @db

    WHILE @@fetch_status = 0

    BEGIN

    --This only exists in relation to the EXEC, not the the outer code

    EXEC ('use [' + @db + ']')

    --Show database that we're in

    SELECT DB_NAME()

    FETCH NEXT

    FROM my_cursor

    INTO @db

    END

    CLOSE my_cursor

    DEALLOCATE my_cursor

    You'll see that the DB_NAME() is always the same, because the USE gets fired in a different batch.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for your help - It makes sense now

    Cheers,

    Bodsda

Viewing 6 posts - 1 through 5 (of 5 total)

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