Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Script Library  Find in which database a table is

Author  Topic  Marcio Starting Member 2 Posts Posted - 2002-04-12 : 17:07:19 The code bellow demonstrates how you are able to find in which database a table is. That is usefull when you have lots of databases, and need to manage communication among objects from those various database. CREATE PROCEDURE sp_findtable ( @TableName varchar(50) = NULL ) AS --sp_findtable 'weblogcache' SET NOCOUNT ON DECLARE @name varchar(50) DECLARE @sql varchar(2000) DECLARE @DBName varchar(50) SELECT @sql = '' SELECT @DBName = '' CREATE Table #temp ( DBName varchar(50), TableName varchar(50) ) DECLARE DB_Cursor SCROLL CURSOR FOR -- Dpt, SELECT [Name] FROM Master.dbo.sysdatabases WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb') OPEN DB_Cursor FETCH FIRST FROM DB_Cursor INTO @Name WHILE ( @@Fetch_Status = 0) BEGIN SELECT @DBName = @Name SELECT @sql =N' INSERT INTO #temp ' SELECT @sql = @sql + ' SELECT '''+@DBName+''', [Name] FROM '+@DBName+'.dbo.sysobjects WHERE type = ''u'' ' EXECUTE (@sql) FETCH NEXT FROM DB_Cursor INTO @Name END CLOSE DB_Cursor DEALLOCATE DB_Cursor IF @TableName IS NOT NULL SELECT * FROM #temp WHERE TableName = @TableName IF @TableName IS NULL SELECT * FROM #temp ORDER BY DBNAME, TableName