FOR EACH LOOP in T-SQL

Hello,
I am inserting a list of database names from sysdatabases into a temp
table, below is the T-SQL.
CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
INSERT INTO ##SpringClean
SELECT DISTINCT dbo.sysdatabases.name
FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
AND dbo.sysdatabases.name NOT IN
('master','model','msdb','Northwind','pubs','tempdb')
I would like to code a loop in T-SQL that will cycle through each database
name in the above temp table and execute the following select
USE (db name from temp table)
SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
FROM sysfiles
GO
I am trying to get an accurate query of the size of my databases. Any help
would be greatly appreciated.
JoeYou can use a cursor for that, and loop the cursor. See DECLARE (CURSOR) in
Books Online. You would not need a temptable for this.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Joe G" <invalid@.invalid.com> wrote in message
news:3fb2525c@.usenet01.boi.hp.com...
> Hello,
> I am inserting a list of database names from sysdatabases into a temp
> table, below is the T-SQL.
> CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> INSERT INTO ##SpringClean
> SELECT DISTINCT dbo.sysdatabases.name
> FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> AND dbo.sysdatabases.name NOT IN
> ('master','model','msdb','Northwind','pubs','tempdb')
> I would like to code a loop in T-SQL that will cycle through each database
> name in the above temp table and execute the following select
> USE (db name from temp table)
> SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> FROM sysfiles
> GO
> I am trying to get an accurate query of the size of my databases. Any
help
> would be greatly appreciated.
> Joe
>
>|||declare @.sql varchar(4000)
declare @.db varchar(64)
set @.db=''
SELECT @.db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
and name > @.db
while @.db is not null
begin
set @.sql='use '+@.db+'
SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
'+@.db+'"
FROM sysfiles'
exec (@.sql)
SELECT @.db=min(name)
FROM sysdatabases
WHERE name NOT IN ('master','msdb','tempdb')
and name > @.db
end
Hope this helps,
Gert-Jan
Joe G wrote:
> Hello,
> I am inserting a list of database names from sysdatabases into a temp
> table, below is the T-SQL.
> CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> INSERT INTO ##SpringClean
> SELECT DISTINCT dbo.sysdatabases.name
> FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> AND dbo.sysdatabases.name NOT IN
> ('master','model','msdb','Northwind','pubs','tempdb')
> I would like to code a loop in T-SQL that will cycle through each database
> name in the above temp table and execute the following select
> USE (db name from temp table)
> SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> FROM sysfiles
> GO
> I am trying to get an accurate query of the size of my databases. Any help
> would be greatly appreciated.
> Joe|||Wow,
Thanks very much, this was extremely helpful. I am now going to try and
figure out what you did in your code. I appreciate the effort.
Joe
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FB27693.F0B27DA1@.toomuchspamalready.nl...
> declare @.sql varchar(4000)
> declare @.db varchar(64)
> set @.db=''
> SELECT @.db=min(name)
> FROM sysdatabases
> WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
> and name > @.db
> while @.db is not null
> begin
> set @.sql='use '+@.db+'
> SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
> '+@.db+'"
> FROM sysfiles'
> exec (@.sql)
> SELECT @.db=min(name)
> FROM sysdatabases
> WHERE name NOT IN ('master','msdb','tempdb')
> and name > @.db
> end
> Hope this helps,
> Gert-Jan
>
> Joe G wrote:
> >
> > Hello,
> >
> > I am inserting a list of database names from sysdatabases into a temp
> > table, below is the T-SQL.
> >
> > CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> > INSERT INTO ##SpringClean
> > SELECT DISTINCT dbo.sysdatabases.name
> > FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > AND dbo.sysdatabases.name NOT IN
> > ('master','model','msdb','Northwind','pubs','tempdb')
> >
> > I would like to code a loop in T-SQL that will cycle through each
database
> > name in the above temp table and execute the following select
> >
> > USE (db name from temp table)
> > SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > FROM sysfiles
> > GO
> >
> > I am trying to get an accurate query of the size of my databases. Any
help
> > would be greatly appreciated.
> >
> > Joe|||PSS.
It worked, I just want to figure out what you did now.
Joe
"Joe G" <invalid@.invalid.com> wrote in message
news:3fb283fc@.usenet01.boi.hp.com...
> Wow,
> Thanks very much, this was extremely helpful. I am now going to try and
> figure out what you did in your code. I appreciate the effort.
> Joe
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FB27693.F0B27DA1@.toomuchspamalready.nl...
> > declare @.sql varchar(4000)
> > declare @.db varchar(64)
> > set @.db=''
> >
> > SELECT @.db=min(name)
> > FROM sysdatabases
> > WHERE name NOT IN ('master','msdb','tempdb') -- all system databases
> > and name > @.db
> >
> > while @.db is not null
> > begin
> >
> > set @.sql='use '+@.db+'
> > SELECT SUM(sysfiles.size * 8/1024) AS "Database Size
> > '+@.db+'"
> > FROM sysfiles'
> > exec (@.sql)
> >
> > SELECT @.db=min(name)
> > FROM sysdatabases
> > WHERE name NOT IN ('master','msdb','tempdb')
> > and name > @.db
> > end
> >
> > Hope this helps,
> > Gert-Jan
> >
> >
> > Joe G wrote:
> > >
> > > Hello,
> > >
> > > I am inserting a list of database names from sysdatabases into a
temp
> > > table, below is the T-SQL.
> > >
> > > CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY KEY)
> > > INSERT INTO ##SpringClean
> > > SELECT DISTINCT dbo.sysdatabases.name
> > > FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > > WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > > AND dbo.sysdatabases.name NOT IN
> > > ('master','model','msdb','Northwind','pubs','tempdb')
> > >
> > > I would like to code a loop in T-SQL that will cycle through each
> database
> > > name in the above temp table and execute the following select
> > >
> > > USE (db name from temp table)
> > > SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > > FROM sysfiles
> > > GO
> > >
> > > I am trying to get an accurate query of the size of my databases. Any
> help
> > > would be greatly appreciated.
> > >
> > > Joe
>|||Joe, another method is this single command... Bruce
exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>--Original Message--
>Hello,
> I am inserting a list of database names from
sysdatabases into a temp
>table, below is the T-SQL.
>CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
KEY)
>INSERT INTO ##SpringClean
>SELECT DISTINCT dbo.sysdatabases.name
>FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
>WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>AND dbo.sysdatabases.name NOT IN
>('master','model','msdb','Northwind','pubs','tempdb')
>I would like to code a loop in T-SQL that will cycle
through each database
>name in the above temp table and execute the following
select
>USE (db name from temp table)
>SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
>FROM sysfiles
>GO
>I am trying to get an accurate query of the size of my
databases. Any help
>would be greatly appreciated.
>Joe
>
>.
>|||I can't seem to run this against a remote server, only my personal copy of
SQL Server located on my laptop. Is this a requirement for this stored
proc?
By the way, this was an amazing command none the less.
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> Joe, another method is this single command... Bruce
> exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>
>
> >--Original Message--
> >Hello,
> >
> > I am inserting a list of database names from
> sysdatabases into a temp
> >table, below is the T-SQL.
> >
> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> KEY)
> >INSERT INTO ##SpringClean
> >SELECT DISTINCT dbo.sysdatabases.name
> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >AND dbo.sysdatabases.name NOT IN
> >('master','model','msdb','Northwind','pubs','tempdb')
> >
> >I would like to code a loop in T-SQL that will cycle
> through each database
> >name in the above temp table and execute the following
> select
> >
> >USE (db name from temp table)
> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >FROM sysfiles
> >GO
> >
> >I am trying to get an accurate query of the size of my
> databases. Any help
> >would be greatly appreciated.
> >
> >Joe
> >
> >
> >
> >.
> >|||Actually, it has nothing to do with me executing it locally, when I execute
it on other databases I get the following error
"Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_MSforeachDB'."
Does anyone know why?
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> Joe, another method is this single command... Bruce
> exec sp_MSforeachDB @.command1="SELECT SUM(size * 8/1024)
> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>
>
> >--Original Message--
> >Hello,
> >
> > I am inserting a list of database names from
> sysdatabases into a temp
> >table, below is the T-SQL.
> >
> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> KEY)
> >INSERT INTO ##SpringClean
> >SELECT DISTINCT dbo.sysdatabases.name
> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >AND dbo.sysdatabases.name NOT IN
> >('master','model','msdb','Northwind','pubs','tempdb')
> >
> >I would like to code a loop in T-SQL that will cycle
> through each database
> >name in the above temp table and execute the following
> select
> >
> >USE (db name from temp table)
> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >FROM sysfiles
> >GO
> >
> >I am trying to get an accurate query of the size of my
> databases. Any help
> >would be greatly appreciated.
> >
> >Joe
> >
> >
> >
> >.
> >|||I'm running it on SQL 2000. I THINK it's available on
SQL 7 also? are you on SQL 2000? Can you see that
proc in the master database? If it's there and you have
permission to run it, not sure why you get that message.
I use the DB and TABLE ForEach procs all the time for
short commands like that... Bruce
>--Original Message--
>Actually, it has nothing to do with me executing it
locally, when I execute
>it on other databases I get the following error
> "Server: Msg 2812, Level 16, State 62, Line 1
>Could not find stored procedure 'sp_MSforeachDB'."
>Does anyone know why?
>
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
>> Joe, another method is this single command... Bruce
>> exec sp_MSforeachDB @.command1="SELECT SUM(size *
8/1024)
>> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
>>
>>
>> >--Original Message--
>> >Hello,
>> >
>> > I am inserting a list of database names from
>> sysdatabases into a temp
>> >table, below is the T-SQL.
>> >
>> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
>> KEY)
>> >INSERT INTO ##SpringClean
>> >SELECT DISTINCT dbo.sysdatabases.name
>> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
>> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
>> >AND dbo.sysdatabases.name NOT IN
>> >('master','model','msdb','Northwind','pubs','tempdb')
>> >
>> >I would like to code a loop in T-SQL that will cycle
>> through each database
>> >name in the above temp table and execute the following
>> select
>> >
>> >USE (db name from temp table)
>> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
>> >FROM sysfiles
>> >GO
>> >
>> >I am trying to get an accurate query of the size of my
>> databases. Any help
>> >would be greatly appreciated.
>> >
>> >Joe
>> >
>> >
>> >
>> >.
>> >
>
>.
>|||Perhaps the SQL Server is case sensitive? The name of the procedure is
sp_MSforeachdb.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:069801c3a98f$5f32fb60$a401280a@.phx.gbl...
> I'm running it on SQL 2000. I THINK it's available on
> SQL 7 also? are you on SQL 2000? Can you see that
> proc in the master database? If it's there and you have
> permission to run it, not sure why you get that message.
> I use the DB and TABLE ForEach procs all the time for
> short commands like that... Bruce
> >--Original Message--
> >Actually, it has nothing to do with me executing it
> locally, when I execute
> >it on other databases I get the following error
> >
> > "Server: Msg 2812, Level 16, State 62, Line 1
> >Could not find stored procedure 'sp_MSforeachDB'."
> >
> >Does anyone know why?
> >
> >
> >
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> >> Joe, another method is this single command... Bruce
> >>
> >> exec sp_MSforeachDB @.command1="SELECT SUM(size *
> 8/1024)
> >> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> > I am inserting a list of database names from
> >> sysdatabases into a temp
> >> >table, below is the T-SQL.
> >> >
> >> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> >> KEY)
> >> >INSERT INTO ##SpringClean
> >> >SELECT DISTINCT dbo.sysdatabases.name
> >> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> >> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> >> >AND dbo.sysdatabases.name NOT IN
> >> >('master','model','msdb','Northwind','pubs','tempdb')
> >> >
> >> >I would like to code a loop in T-SQL that will cycle
> >> through each database
> >> >name in the above temp table and execute the following
> >> select
> >> >
> >> >USE (db name from temp table)
> >> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> >> >FROM sysfiles
> >> >GO
> >> >
> >> >I am trying to get an accurate query of the size of my
> >> databases. Any help
> >> >would be greatly appreciated.
> >> >
> >> >Joe
> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Another save by the good of the community. I was so deep into the issue at
hand yesterday I didn't even think to check the case sensitivity. That was
the issue. I remember inspecting all of the databases it was running
against and finding the stored proc but I couldn't figure out why it
wouldn't run. Case sensitivity.
Thanks a million.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OgiVv7bqDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Perhaps the SQL Server is case sensitive? The name of the procedure is
> sp_MSforeachdb.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> news:069801c3a98f$5f32fb60$a401280a@.phx.gbl...
> > I'm running it on SQL 2000. I THINK it's available on
> > SQL 7 also? are you on SQL 2000? Can you see that
> > proc in the master database? If it's there and you have
> > permission to run it, not sure why you get that message.
> > I use the DB and TABLE ForEach procs all the time for
> > short commands like that... Bruce
> >
> > >--Original Message--
> > >Actually, it has nothing to do with me executing it
> > locally, when I execute
> > >it on other databases I get the following error
> > >
> > > "Server: Msg 2812, Level 16, State 62, Line 1
> > >Could not find stored procedure 'sp_MSforeachDB'."
> > >
> > >Does anyone know why?
> > >
> > >
> > >
> > >
> > >
> > >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> > >news:045301c3a960$5bbeeea0$a301280a@.phx.gbl...
> > >> Joe, another method is this single command... Bruce
> > >>
> > >> exec sp_MSforeachDB @.command1="SELECT SUM(size *
> > 8/1024)
> > >> AS 'Database Size', '?' FROM ?.dbo.sysfiles"
> > >>
> > >>
> > >>
> > >>
> > >> >--Original Message--
> > >> >Hello,
> > >> >
> > >> > I am inserting a list of database names from
> > >> sysdatabases into a temp
> > >> >table, below is the T-SQL.
> > >> >
> > >> >CREATE TABLE ##SpringClean(DBName varchar(30) PRIMARY
> > >> KEY)
> > >> >INSERT INTO ##SpringClean
> > >> >SELECT DISTINCT dbo.sysdatabases.name
> > >> >FROM dbo.sysdatabases, dbo.sysaltfiles (NOLOCK)
> > >> >WHERE dbo.sysdatabases.dbid = dbo.sysaltfiles.dbid
> > >> >AND dbo.sysdatabases.name NOT IN
> > >> >('master','model','msdb','Northwind','pubs','tempdb')
> > >> >
> > >> >I would like to code a loop in T-SQL that will cycle
> > >> through each database
> > >> >name in the above temp table and execute the following
> > >> select
> > >> >
> > >> >USE (db name from temp table)
> > >> >SELECT SUM(sysfiles.size * 8/1024) AS 'Database Size'
> > >> >FROM sysfiles
> > >> >GO
> > >> >
> > >> >I am trying to get an accurate query of the size of my
> > >> databases. Any help
> > >> >would be greatly appreciated.
> > >> >
> > >> >Joe
> > >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>