|
Marcio
Starting Member
USA
2 Posts |
 Posted - 04/12/2002 : 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
|
|
Find in which database a table is
