SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find in which database a table is
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marcio
Starting Member

USA
2 Posts

Posted - 04/12/2002 :  17:07:19  Show Profile  Reply with Quote
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



  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000