SQLTeam.com | Weblogs | Forums

Check if table exists across all databases

tsql

#1

Hi,

How can I find if a table exists without specifying the database?

Thanks


#2

You could use dynamic code

See if this helps[code]DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql +=
N'SELECT ''' + SD.name + ''' AS Databasename, name FROM ' + QUOTENAME(SD.name) + '.sys.tables '
+ 'WHERE type = ''U'' AND is_MS_Shipped = 0 ORDER BY name; '

  • CHAR(13) + CHAR(10)
    FROM sys.databases SD;

print @sql;

--EXECUTE sp_executesql @sql;
[/code]

Edited to add database name


#3

[code]declare
@TblName sysname = N'MyTable', -- << << << << Set this value
@sqlstmt nvarchar(max)

set @sqlstmt = N'use [?];
select DB_Name() DatabaseName
from sys.tables t
where t.name = ''' + @TblName + ''';'

exec sp_msforeachdb @sqlstmt[/code]You could add some logic to only return results in databases that had the table with an IF EXISTS(..) construct but I'll leave that as an exercise.


#4

May this help you:

IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END


#5

Also refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10832/simulating-undocumented-procedures.aspx