adding a "where"-clause to specific table dynamic

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

Development Tools

ASP.NET

adding a "where"-clause to specific table dynamic

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-06-28 : 03:21:50

Hi Y'all,

I want to get all database-names from my server, i do that like this:

select name, dbid from sysdatabases => from my master database

Now i want to add a "where"-clause to select only those databases that have the table "TASKS".

Can someone help me out?

THanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-28 : 06:15:03

Again, clearly explain what you are trying to do. Is it related to your other topic (dynamically create procedure)?

Madhivanan

Failing to plan is Planning to fail

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-06-28 : 10:24:42

No where its related with the other topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 02:23:18

[code]Declare @t table(query varchar(8000))
Declare @sql varchar(8000)
select @sql=''

select @sql=@sql+'Select distinct '''+name+''' as db from '+name+'.information_schema.tables where table_name = ''TASKS'' union all '
from master..sysdatabases

Select @sql='Select db from ('+left(@sql,len(@sql)-10)+') T where db is not null'
exec(@sql)

[/code]

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-29 : 02:34:39

quote:

Originally posted by madhivanan

Declare @t table(query varchar(8000))
Declare @sql varchar(8000)
select @sql=''

select @sql=@sql+'Select distinct '''+name+''' as db from '+name+'.information_schema.tables where table_name = ''TASKS'' union all ' 
		from master..sysdatabases where has_dbaccess(name) = 1

Select @sql='Select db from ('+left(@sql,len(@sql)-10)+') T where db is not null'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 03:55:46

Thanks Harsh

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-29 : 03:59:16

You welcome!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 09:05:04

I just noticed that there is no need of db is null condition

Declare @t table(query varchar(8000))
Declare @sql varchar(8000)
select @sql=''

select @sql=@sql+'Select distinct '''+name+''' as db from '+name+'.information_schema.tables where table_name = ''TASKS'' union all ' 
		from master..sysdatabases

Select @sql='Select db from ('+left(@sql,len(@sql)-10)+') T where has_dbaccess(name) = 1'
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources