Hi,
How can I find if a table exists without specifying the database?
Thanks
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
[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.
May this help you:
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END