I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it into
a variable after the 'use ' keyword. My test code thus far looks like this:
begin
declare @.db_name varchar(200)
declare @.codevarchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
end
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John Miceli
Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
inside the same EXEC.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it into
> a variable after the 'use ' keyword. My test code thus far looks like this:
> ----
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli

I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it into
a variable after the 'use ' keyword. My test code thus far looks like this:
----
begin
declare @.db_name varchar(200)
declare @.code varchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
en
----
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John MiceliUse is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
inside the same EXEC.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it into
> a variable after the 'use ' keyword. My test code thus far looks like this:
> ----
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli|||Thank you Tibor for your response!
If I have to put all of what follows into the same exec, I think I'm going
to have problems because there are multiple, very large queries that have to
run against the same database. Then when the next database cycles through,
the same statements have to be run against each one (10 total).
Do you have any other ideas of how to make that persistent? Thanks again!
"Tibor Karaszi" wrote:
> Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
> inside the same EXEC.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
> news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
> >I am trying to make a dynamic 'use' statement so that some code can loop
> > through the databases listed in sysdatabases, pull the name and place it into
> > a variable after the 'use ' keyword. My test code thus far looks like this:
> > ----
> > begin
> > declare @.db_name varchar(200)
> > declare @.code varchar(200)
> >
> > set @.db_name = 'nic_int_ldr'
> > select @.code = 'use ' + rtrim(ltrim(@.db_name))
> > print @.code
> > exec (@.code)
> >
> > --use nic_int_ldr
> > select max(id_bat) from ldr_batch
> > end
> > ----
> > At this point I'm just trying to get it to work with a hard coded database
> > name and I can't even get that to go.
> >
> > Is it not possible to make a 'use ' statement work this way?
> >
> > Thanks,
> > John Miceli
>
>|||> Do you have any other ideas of how to make that persistent?
Not that I can think of, I'm afraid... Sorry :-(
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<John Miceli>; "MCDBA" <jdmiceli@.hotmail.com> wrote in message
news:BA5C17DB-F769-47DA-AF09-7AA414561BBE@.microsoft.com...
> Thank you Tibor for your response!
> If I have to put all of what follows into the same exec, I think I'm going
> to have problems because there are multiple, very large queries that have to
> run against the same database. Then when the next database cycles through,
> the same statements have to be run against each one (10 total).
> Do you have any other ideas of how to make that persistent? Thanks again!
>
> "Tibor Karaszi" wrote:
> > Use is local to a batch, and EXEC is effectively a batch. Put all the thing you want to execute
> > inside the same EXEC.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in message
> > news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
> > >I am trying to make a dynamic 'use' statement so that some code can loop
> > > through the databases listed in sysdatabases, pull the name and place it into
> > > a variable after the 'use ' keyword. My test code thus far looks like this:
> > > ----
> > > begin
> > > declare @.db_name varchar(200)
> > > declare @.code varchar(200)
> > >
> > > set @.db_name = 'nic_int_ldr'
> > > select @.code = 'use ' + rtrim(ltrim(@.db_name))
> > > print @.code
> > > exec (@.code)
> > >
> > > --use nic_int_ldr
> > > select max(id_bat) from ldr_batch
> > > end
> > > ----
> > > At this point I'm just trying to get it to work with a hard coded database
> > > name and I can't even get that to go.
> > >
> > > Is it not possible to make a 'use ' statement work this way?
> > >
> > > Thanks,
> > > John Miceli
> >
> >
> >

I need to select database name from sysdatabases & make dynamic 'u

I am trying to make a dynamic 'use' statement so that some code can loop
through the databases listed in sysdatabases, pull the name and place it int
o
a variable after the 'use ' keyword. My test code thus far looks like this:
----
begin
declare @.db_name varchar(200)
declare @.code varchar(200)
set @.db_name = 'nic_int_ldr'
select @.code = 'use ' + rtrim(ltrim(@.db_name))
print @.code
exec (@.code)
--use nic_int_ldr
select max(id_bat) from ldr_batch
end
----
--
At this point I'm just trying to get it to work with a hard coded database
name and I can't even get that to go.
Is it not possible to make a 'use ' statement work this way?
Thanks,
John MiceliUse is local to a batch, and EXEC is effectively a batch. Put all the thing
you want to execute
inside the same EXEC.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Miceli, MCDBA" <JohnMiceliMCDBA@.discussions.microsoft.com> wrote in me
ssage
news:AA82D9E1-715C-4AE4-B69D-901E989763A5@.microsoft.com...
>I am trying to make a dynamic 'use' statement so that some code can loop
> through the databases listed in sysdatabases, pull the name and place it i
nto
> a variable after the 'use ' keyword. My test code thus far looks like thi
s:
> ----
--
> begin
> declare @.db_name varchar(200)
> declare @.code varchar(200)
> set @.db_name = 'nic_int_ldr'
> select @.code = 'use ' + rtrim(ltrim(@.db_name))
> print @.code
> exec (@.code)
> --use nic_int_ldr
> select max(id_bat) from ldr_batch
> end
> ----
--
> At this point I'm just trying to get it to work with a hard coded database
> name and I can't even get that to go.
> Is it not possible to make a 'use ' statement work this way?
> Thanks,
> John Miceli

I need help with my query

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

Hi,

If you are trying to get the list of tables, database name etc. in your ASP.Net application then have a look at it.http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

Thanks and best regards,

|||

this doesn't seem to work:

SET @.sSql = 'select * from ' + @.databasenaam + '.dbo.sysobjects where name like '''tablesearchnaam''' '

if exists(EXEC sp_sqlexec @.sSql)

|||Have you tried this already?

SELECT*FROM sysobjectsWHERE [name]LIKE'%tasks%'

This can also be done without using a stored procedure...

IFEXISTS(SELECT*FROM sysobjectsWHERE [name]LIKE'%tasks%')
DO STUFF

|||

If you just want tables you might want to restrict the type to 'U'.

SELECT*FROM sysobjectsWHERE [name]LIKE'%tasks%' AND Type = 'U'