i need to create a rowset containing database names where a particualr table name exists.
ie. in Oracle i would select owner from sys.all_tables where table_name='MYTABLE'
I know master.dbo.sysdatabases contains my owners, and in each database sysobjects would identify MYTABLE ..
sp_tables only lists those in the current database, but i want to list all databases that contain MYTABLE
what i meant was, if you write anything based on what you think is in any of the system catalog tables, you stand a chance to have it break if the structure of the system catalog tables changes, which the database vendor can do from time to time
INFORMATION_SCHEMA references, however, are standard sql, and yes, they are views of the system catalog tables, but they will always work, as they will be redefined by the vendor should a change to the underlying system catalg tables occur
furthermore, INFORMATION_SCHEMA is supported by many databases, so you could actually pick up a script you wrote in one and use it in another without change