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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply