sql server2000 search a table 1

[duchovnick]

Hi,
I'm looking for a table which name is, say, "mytable". It is located in one of my databases but i dont know which one. Is there some "search" command to find a table through its name unknowingly its database ?
Thanks.

 

[dhulbert]

If you serach the INFORMATION_SCHEMA.TABLES on th emaster database of your server where TABLE_NAME is wt ever table you are looking for then it will return
TABLE_CATALOG (the database)
TABLE_SCHEMA (the schema)



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[bborissov]

sp_MSForEachDB [COLOR=red]'IF OBJECT_ID(''?.dbo.YourTableNameHere'') IS NOT NULL SELECT ''?'' AS DataBaseName'[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[duchovnick]

Hi Borislav !
I ran:

exec sp_MSForEachDB
'IF OBJECT_ID(''?.dbo.contact1'') IS NOT NULL SELECT ''?'' AS DataBaseName'

and recieved:

The command(s) completed successfully.

Does it mean the table doesnt exist ? I didnt run the comman as properly?
Thanks !

 

[dhulbert]

It means either the table doesn't exist or it does exist but not in the dbo. schema.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[bborissov]

How about:

[COLOR=blue]exec[/color] sp_MSForEachDB
[COLOR=red]'IF OBJECT_ID(''?..contact1'') IS NOT NULL SELECT ''?'' AS DataBaseName'[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[duchovnick]

The same results Borislav..
I guess the table is lost..
And dhulbert how do i know what schema i'm in and which ones is there ? I didnt see any "schema" in query analyzer or server enterprise.
Thanks however.

 

[dhulbert]

Ok

Tried and tested, this one will serach every databsae for an objce with the name you specify. It will return views, procedures etc. but you could amend it to jsu tpull out tables.

USE MASTER

DECLARE @DBNAME AS NVARCHAR(50)
DECLARE @DYNSQL AS NVARCHAR(250)
DECLARE TABLE_CURSOR CURSOR FOR
	SELECT	[NAME]
	FROM	MASTER..SYSDATABASES;
	OPEN TABLE_CURSOR
		FETCH NEXT FROM TABLE_CURSOR
			WHILE @@FETCH_STATUS  = 0
				BEGIN
					FETCH NEXT FROM TABLE_CURSOR
					INTO @DBNAME;
					SET @DYNSQL = 
					'SELECT ''' + @DBNAME + ''', NAME 
					FROM [' + @DBNAME + ']..SYSOBJECTS
					WHERE NAME LIKE  ''%YOUR TABLE NAME%'''
					EXEC (@DYNSQL);
				END;
	CLOSE TABLE_CURSOR;
	DEALLOCATE TABLE_CURSOR;

Give it a try it should work

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[duchovnick]

Hi dhulbert ,
Your code worked. i dont entirely understand it but i'll try to do so later.
Thanks a lot !