Cursors - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Cursors

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-26 : 03:08:48

Hello Everyone,

I was writing the procedure to find out, on which database a particular table exists.

The code is quite simple, but i think i have missed out somthing due to which its running into endless loop

If anyone figure it out then please let me know ..


Create Proc FindTables
(
 @pTableName VarChar(800) -- Variable used for finding the table name 
)
As 
Begin 

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query 
Declare @DbName Varchar(800) -- Variable for storing the database Name 
Declare @Count nvarchar(20) -- Variable for Counting whether the table exists 
Declare @PramDefination Varchar(100)

Declare @TblDataBase Table 
(DbName varchar(1000) )

Declare FindTable CurSor 
For Select [Name] From Master..SysDatabases 

Open FindTable 
Fetch Next From FindTable Into @DbName 
While (@@Fetch_Status= 0)
Begin 
	Set @QryString = N'Select  @Count = Count(1) From [' + @DbName + ']..SysObjects Where [Name] Like ''%' 
			+ @pTableName + '%'''
	print @QryString
	EXEC sp_executesql @QryString, N'@Count as TinyInt OUT' , @Count OUT
	print @Count
	if (Convert(int,@Count)>=1)
	Begin 
		Insert @TblDataBase 
		Select @DbName
	End 
Fetch Next From FindTable Into @DbName
End	
Close FindTable
DeAllocate FindTable 

Select Dbname As 'DataBase Name ' From @TblDatabase
End

Thanks ..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-26 : 03:50:29

How about something like
Create Proc FindTables
(
@pTableName VarChar(800) -- Variable used for finding the table name
)
As

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
Declare @DbName Varchar(800) -- Variable for storing the database Name

create table #TblDataBase
(DbName varchar(1000) )

select @DbName = ''
while @DbName < (select max(name) from master..sysdatabases)
begin
select @DbName = min(name) from master..sysdatabases where DbName > @DbName
select @QryString = 'select ' + @DbName + ' where exists (select * From [' + @DbName + ']..SysObjects Where [Name] Like ''%'
+ @pTableName + '%'' and xtype = ''U'')'
insert #TblDataBase exec (@QryString)
End
Select Dbname As 'DataBase Name ' From #TblDataBase

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 03:52:08

Or this

declare	@table varchar(50), @cmd varchar(500)

create table #temp(dbname varchar(50), tbname varchar(50))

select	@table = 'Orders'
select	@cmd = 'USE ? SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like ''%' + @table + '%'''

insert into #temp exec sp_MSforeachdb @command1 = @cmd

select * from #temp

drop table #temp

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-26 : 04:17:43

NR:

quote:

Create Proc FindTables
(
@pTableName VarChar(800) -- Variable used for finding the table name
)
As

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
Declare @DbName Varchar(800) -- Variable for storing the database Name

create table #TblDataBase
(DbName varchar(1000) )

select @DbName = ''
while @DbName < (select max(name) from master..sysdatabases)
begin
select @DbName = min(name) from master..sysdatabases where DbName > @DbName
select @QryString = 'select ' + @DbName + ' where exists (select * From [' + @DbName + ']..SysObjects Where [Name] Like ''%'
+ @pTableName + '%'' and xtype = ''U'')'
insert #TblDataBase exec (@QryString)
End
Select Dbname As 'DataBase Name ' From #TblDataBase

There were some error which i was getting while i excute your procedure..
so i modified it little bit but the desired output is not what i wanted...
Here is the code..


Alter Proc FindTables
(
@pTableName VarChar(800) -- Variable used for finding the table name
)
As

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
Declare @DbName Varchar(800) -- Variable for storing the database Name

create table #TblDataBase
(DbName varchar(1000) )

select @DbName = ''
while @DbName < (select max(name) from master..sysdatabases)
begin
select @DbName = min(name) from master..sysdatabases where [Name] > @DbName
select @QryString = 'select [Name]  From master..sysdatabases where exists (select * From [' + @DbName + ']..SysObjects Where [Name] Like ''%'
+ @pTableName + '%'' and xtype = ''U'')'
print @QryString
insert #TblDataBase exec (@QryString)
End
Select Dbname As 'DataBase Name ' From #TblDataBase

KHTAN:
When i excuted your code, i got following error..
the code what i excuted is here..

quote:

Could not locate entry in sysdatabases for database 'Saks'. No entry found with that name. Make sure that the name is entered correctly.


declare	@table varchar(50), @cmd varchar(500)

create table #temp(dbname varchar(50), tbname varchar(50))

select	@table = 'RcEmp'
select	@cmd = 'USE ? SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like ''%' + @table + '%'''

insert into #temp exec sp_MSforeachdb @command1 = @cmd

select * from #temp

drop table #temp

And as always i never understand the solution what you provide me, so i couldnt modify your code..

and it would be great if you can explain me

Thanks again for the quick help ..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-26 : 04:18:37

Tan, sp_MSforeachdb is undocumented

Also, Nigel is correct with the following changes


begin
     select @DbName = min(name) from master..sysdatabases where Name > @DbName
     select @QryString = 'select ''' + @DbName + ''' where exists (select * From [' + @DbName  + ']..SysObjects Where [Name] Like ''%' 
     + @pTableName + '%'' and xtype = ''U'')'
     insert #TblDataBase exec (@QryString)
End

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-26 : 04:25:33

Thanks Maddy and Nigel it worked for me ..

the Final Procedure looks something like this ..



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FindTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[FindTables]
GO
CREATE Proc FindTables
(
@pTableName VarChar(800) -- Variable used for finding the table name
)
As

Declare @QryString nVarchar(4000) -- Variable will be used for the dynamic query
Declare @DbName Varchar(800) -- Variable for storing the database Name

create table #TblDataBase
(DbName varchar(1000) )

select @DbName = ''
while @DbName < (select max(name) from master..sysdatabases)
begin
select @DbName = min(name) from master..sysdatabases where [Name] > @DbName
     select @QryString = 'select ''' + @DbName + ''' where exists (select * From [' + @DbName  + ']..SysObjects Where [Name] Like ''%' 
     + @pTableName + '%'' and xtype = ''U'')'
     insert #TblDataBase exec (@QryString)
End
Select Dbname As 'DataBase Name ' From #TblDataBase

But Tan i would really like to know what code you wrote it complete Greek to me..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 04:44:59

chiragkhabaria,

A thousand apology. I should have try to explain more clearly in my post.

Basically sp_MSforeachdb is a undocumented sp. So meaning, it may not be available in future version release.

You can refer to the following 2 link for more info.
http://www.databasejournal.com/features/mssql/article.php/3441031
http://www.sql-server-performance.com/ak_find_sql_server_database_objects.asp

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-26 : 07:17:12

Thanks Tan, I will take a look on the articles.. :)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources