Tables | TroubleshootingSQL

Once in a while I find that need to find out if a particular table name exists in any of the databases of my SQL Server instance. This is when sp_MSforeachdb comes into play.

EXEC sp_MSforeachdb '
if exists(select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0)
begin
    print ''Database Name: '' + ''?''
    select * from ?.sys.objects where type = ''U'' and name like ''%log%%'' and is_ms_shipped = 0
end'

The above piece of T-SQL code finds out if there exists a user table which has a string “log” in it’s name on all the databases of the instance. You can keep modifying this query by adding more filters in the WHERE clause. Current filters only looks for tables which are not shipped by Microsoft (Eg. user tables created by replication, database tuning advisor, log shipping etc.). Basically any user table created by you. If you want to use this on a SQL Server 2000 instance, then you would need to use sysobjects instead of sys.objects.

select object_name(a.object_id) as ObjectName, IndexName = case when a.name is null then 'Heap' else a.name end,(select name from sys.data_spaces where data_space_id = a.data_space_id) as FileGroupName from sys.indexes a where index_id < 2