January 6, 2012 at 2:47 am
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
January 6, 2012 at 3:01 am
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).
January 6, 2012 at 3:14 am
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)
January 6, 2012 at 3:14 am
Thanks for that, it worked. But why didn't my cursor work?
Thanks,
Bodsda
January 6, 2012 at 3:19 am
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.
January 6, 2012 at 3:26 am
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