Hi All,
I want to Sum instead of Join Parameter arrays selected values and use
it within the report to display data based on total sum of all the
values selected. Join concatanates them with comma delimited values
but i want sum of all those values.
Example as below
Value DisplayText
1 - Current Month
2 - Previoius Year
4 - Half Year
8 - Year to date
so if
current month and half year
are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
get by join function.
Code = SUM(Parameters!dropdownbox.value)
Result = #Error
Code = Join(Parameters!dropdownbox.value,",")
Result = "1,4"
Code = SUM(Join(Parameters!dropdownbox.value,","))
Result = #Error
Code = Parameters!dropdownbox.count
Result = 4 (it gives me length of array)
Code = ?
Result = 5 (This is the result is want...but can make it work)
Any help greatly appreciated
Regards
Nirav Lulla
Yotta ConsultingOn May 14, 1:02 pm, nlulla <nirav.lu...@.gmail.com> wrote:
> Hi All,
> I want to Sum instead ofJoinParameterarrays selected values and use
> it within the report to display data based on total sum of all the
> values selected.Joinconcatanates them with comma delimited values
> but i want sum of all those values.
> Example as below
> Value DisplayText
> 1 - Current Month
> 2 - Previoius Year
> 4 - Half Year
> 8 - Year to date
> so if
> current month and half year
> are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
> get byjoinfunction.
> Code = SUM(Parameters!dropdownbox.value)
> Result = #Error
> Code =Join(Parameters!dropdownbox.value,",")
> Result = "1,4"
> Code = SUM(Join(Parameters!dropdownbox.value,","))
> Result = #Error
> Code = Parameters!dropdownbox.count
> Result = 4 (it gives me length of array)
> Code = ?
> Result = 5 (This is the result is want...but can make it work)
> Any help greatly appreciated
> Regards
> Nirav Lulla
> Yotta Consulting
I'm not sure how many items are in your dropdown list but this will
work if you only have a few...
=CInt(Parameters!site.Value(0)) + CInt(Parameters!site.Value(1))|||Hi James,
Thanks for posting your reply, you suggesstio would only work if i
have set fixed length of options, but i don't know how this is going
to work in case of unknown number of options.
For now , I have Created a .net class file DLL with following code and
referenced the dll in my .rdl file, it works for me, but would be good
if it can be done within SSRS itself. Any more suggesstions welcome
Nirav Lulla
Yotta Consulting
Public Class ClsCommon
Const bDisplayColumn As Boolean = False
Const bHideColumn As Boolean = True
Shared Function SumOfArrayString(ByVal ArrayString As String) As
Integer
Dim arylist As System.Array
Dim sum As Integer
Try
arylist = ArrayString.Split(",")
For Each item As Integer In arylist
sum += CInt(item)
Next
Catch ex As Exception
Return -1
Finally
Return sum
End Try
End Function
End Class
Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Addition of strings
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
This cannot be done in a single query unless you employ some tricky hack. There is no obvious and reliable solution to do this in a single query. You'll have to use a cursor and a table variable for this purpose.
|||Hi,
with .Net 2.0 it is very easy. You can write "User defined aggregate" called for example StringConcat and use it in your queries like this
SELECT dbo.StringConcat(field2)FROM MyTableGROUP BY field1
and here is the code for your aggregate
C#
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text;using System.IO;using System.Runtime.InteropServices;[Serializable][SqlUserDefinedAggregate( Format.UserDefined,// user-defined serialization IsInvariantToDuplicates =false,// duplicates matter IsInvariantToNulls =true,// don't care about NULLs IsInvariantToOrder =false,// order matters (ignored) IsNullIfEmpty =false,// don't yield NULL if empty set MaxByteSize = 8000)]// maximum size in bytespublic struct StringConcat : IBinarySerialize{private StringBuilder sb;public void Init() {this.sb =new StringBuilder(); }public void Accumulate(SqlString s) {if (s.IsNull) {return;// skip NULLs }else {this.sb.Append(s.Value); } }public void Merge(StringConcat Group) {this.sb.Append(Group.sb); }public SqlString Terminate() {return new SqlString(this.sb.ToString()); }public void Read(BinaryReader r) { sb =new StringBuilder(r.ReadString()); }public void Write(BinaryWriter w) {if (this.sb.Length > 4000)// limit sb to 8000 bytes w.Write(this.sb.ToString().Substring(0, 4000));else w.Write(this.sb.ToString()); }}// end StringConcatVB
Imports SystemImports System.DataImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.TextImports System.IOImports System.Runtime.InteropServices' user-defined serialization' duplicates matter' don't care about NULLs' order matters (ignored) ' don't yield NULL if empty set' maximum size in bytes is 8000<Serializable(), _ SqlUserDefinedAggregate( _ Format.UserDefined, _ IsInvariantToDuplicates:=False, _ IsInvariantToNulls:=True, _ IsInvariantToOrder:=False, _ IsNullIfEmpty:=False, _ MaxByteSize:=8000)> _Public Structure StringConcatImplements IBinarySerializePrivate sbAs StringBuilderPublic Sub Init()Me.sb =New StringBuilder()End Sub Public Sub Accumulate(ByVal sAs SqlString)If s.IsNullThen Return' skip NULLsElse Me.sb.Append(s.Value)End If End Sub Public Sub Merge(ByVal GroupAs StringConcat)Me.sb.Append(Group.sb)End Sub Public Function Terminate()As SqlStringReturn New SqlString(sb.ToString())End Function Public Sub Read(ByVal rAs BinaryReader) _Implements IBinarySerialize.Read sb =New StringBuilder(r.ReadString())End Sub Public Sub Write(ByVal wAs BinaryWriter) _Implements IBinarySerialize.WriteIf Me.sb.Length > 4000Then' limit sb to 8000 bytes w.Write(Me.sb.ToString().Substring(0, 4000))Else w.Write(Me.sb.ToString())End If End SubEnd Structure' end StringConcat
I hope this helps.
Let me know if this worked for you.
|||thanks, But I am looking for something within the sql
|||Hi,
After compiling the class into a DLL, you can import the DLL as a SQL Server assembly using either the Visual Studio 2005 Deploy option or manually using the CREATE ASSEMBLY statement and CREATE AGGREGATE statement as is shown in the following listing:
CREATE ASSEMBLY StringConcatFROM'C:\StringConcat.dll'GOCREATE AGGREGATE StringConcat(@.inputnvarchar)RETURNSnvarcharEXTERNALNAME StringConcat.StringConcatGO
Using CLR objects (functions, aggregates...) for string manipulations or complex calculations is way faster than using nested queries or Cursors.
I have used this aggregate in my projects and it works fine.
|||Hi, jiju-kj-
Of course it is possible to achieve this within the SQL. But you will need nested cursors the first cursor will iterate through distinct values in field1 and the inner cursor will iterate through all values from field2 for the current value in field1. I don't recommend you using cursors. But will decide which way is better for you. Let me know if you have problems with creating the cursors.
Cheers,
Paul
sql
Thursday, March 22, 2012
adding two sum() 'ed values
Hi all,
if I have
select sum(ammount) from claimfinancialpayment where claimid = 10
select sum(ammount) from claimpaymenthistory where claimid = 10
How can I add the two summed values together, when
select sum(ammount) from claimfinancialpayment where claimid = 10
sums three rows,
and
select sum(ammount) from claimpaymenthistory where claimid = 10
sums 5 rows
I was thinking of
declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
set @.tot = @.sum1+@.sum2
Would this be correct or is there an easier or faster way of doing this
THanks
RObertRobert
SELECT SUM(bblala)
FROM
(
select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
UNION ALL
select sum(ammount) from claimpaymenthistory where claimid = 10
) AS Der
"Robert Bravery" <me@.u.com> wrote in message
news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> if I have
> select sum(ammount) from claimfinancialpayment where claimid = 10
> select sum(ammount) from claimpaymenthistory where claimid = 10
> How can I add the two summed values together, when
> select sum(ammount) from claimfinancialpayment where claimid = 10
> sums three rows,
> and
> select sum(ammount) from claimpaymenthistory where claimid = 10
> sums 5 rows
> I was thinking of
> declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
> select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
> select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
> set @.tot = @.sum1+@.sum2
> Would this be correct or is there an easier or faster way of doing this
> THanks
> RObert
>
>|||You should use UNION instead of UNION ALL there.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
>|||If both SELECTs return the same value, you will lose one of them using UNION
instead of UNION ALL as a duplicate will be discarded.
BTW Uri, you appear to have mistyped the column name for the outer SUM, your
query will error due to it trying to sum column bblala when the only column
is blbla.
SELECT SUM(blabla)
FROM
(
select sum(ammount) as blabla from claimfinancialpayment where claimid =
10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
10 ) AS Der
Dan
Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uahTBFQQGHA.140@.TK2M
SFTNGP12.phx.gbl...|||Hi ,Dan
Yes , I missed 'a' , bit I think the OP got the idea:-)))
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ed6hgSQQGHA.2496@.TK2MSFTNGP11.phx.gbl...
> If both SELECTs return the same value, you will lose one of them using
> UNION instead of UNION ALL as a duplicate will be discarded.
> BTW Uri, you appear to have mistyped the column name for the outer SUM,
> your query will error due to it trying to sum column bblala when the only
> column is blbla.
> SELECT SUM(blabla)
> FROM
> (
> select sum(ammount) as blabla from claimfinancialpayment where claimid =
> 10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
> 10 ) AS Der
>
> Dan
>
> Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
>
>|||Roji
No , you won't get an exected result as I understood the OP
try
USE northwind
select sum(ord)
from
(
select sum(orderid) ord from orders
union --all
select sum(orderid) from orders
) as d
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry my mistake.
Infact you should use UNION ALL instead of UN ION :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eRIcksQQGHA.5116@.TK2MSFTNGP10.phx.gbl...
> Roji
> No , you won't get an exected result as I understood the OP
> try
> USE northwind
> select sum(ord)
> from
> (
> select sum(orderid) ord from orders
> union --all
> select sum(orderid) from orders
> ) as d
>
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>|||Just for variety, another alternative.
SELECT GrandTotal =
(select sum(ammount) from claimfinancialpayment
where claimid = 10) +
(select sum(ammount) from claimpaymenthistory
where claimid = 10)
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 11:38:44 +0200, "Robert Bravery" <me@.u.com> wrote:
>Hi all,
>if I have
>select sum(ammount) from claimfinancialpayment where claimid = 10
>select sum(ammount) from claimpaymenthistory where claimid = 10
>How can I add the two summed values together, when
>select sum(ammount) from claimfinancialpayment where claimid = 10
>sums three rows,
>and
>select sum(ammount) from claimpaymenthistory where claimid = 10
>sums 5 rows
>I was thinking of
>declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
>select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
>select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
>set @.tot = @.sum1+@.sum2
>Would this be correct or is there an easier or faster way of doing this
>THanks
>RObert
>|||THanks Uri, just what I needed
RObert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
10
>sql
if I have
select sum(ammount) from claimfinancialpayment where claimid = 10
select sum(ammount) from claimpaymenthistory where claimid = 10
How can I add the two summed values together, when
select sum(ammount) from claimfinancialpayment where claimid = 10
sums three rows,
and
select sum(ammount) from claimpaymenthistory where claimid = 10
sums 5 rows
I was thinking of
declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
set @.tot = @.sum1+@.sum2
Would this be correct or is there an easier or faster way of doing this
THanks
RObertRobert
SELECT SUM(bblala)
FROM
(
select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
UNION ALL
select sum(ammount) from claimpaymenthistory where claimid = 10
) AS Der
"Robert Bravery" <me@.u.com> wrote in message
news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> if I have
> select sum(ammount) from claimfinancialpayment where claimid = 10
> select sum(ammount) from claimpaymenthistory where claimid = 10
> How can I add the two summed values together, when
> select sum(ammount) from claimfinancialpayment where claimid = 10
> sums three rows,
> and
> select sum(ammount) from claimpaymenthistory where claimid = 10
> sums 5 rows
> I was thinking of
> declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
> select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
> select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
> set @.tot = @.sum1+@.sum2
> Would this be correct or is there an easier or faster way of doing this
> THanks
> RObert
>
>|||You should use UNION instead of UNION ALL there.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
>|||If both SELECTs return the same value, you will lose one of them using UNION
instead of UNION ALL as a duplicate will be discarded.
BTW Uri, you appear to have mistyped the column name for the outer SUM, your
query will error due to it trying to sum column bblala when the only column
is blbla.
SELECT SUM(blabla)
FROM
(
select sum(ammount) as blabla from claimfinancialpayment where claimid =
10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
10 ) AS Der
Dan
Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uahTBFQQGHA.140@.TK2M
SFTNGP12.phx.gbl...|||Hi ,Dan
Yes , I missed 'a' , bit I think the OP got the idea:-)))
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ed6hgSQQGHA.2496@.TK2MSFTNGP11.phx.gbl...
> If both SELECTs return the same value, you will lose one of them using
> UNION instead of UNION ALL as a duplicate will be discarded.
> BTW Uri, you appear to have mistyped the column name for the outer SUM,
> your query will error due to it trying to sum column bblala when the only
> column is blbla.
> SELECT SUM(blabla)
> FROM
> (
> select sum(ammount) as blabla from claimfinancialpayment where claimid =
> 10 UNION ALL select sum(ammount) from claimpaymenthistory where claimid =
> 10 ) AS Der
>
> Dan
>
> Roji. wrote on Mon, 6 Mar 2006 15:23:42 +0530:
>
>|||Roji
No , you won't get an exected result as I understood the OP
try
USE northwind
select sum(ord)
from
(
select sum(orderid) ord from orders
union --all
select sum(orderid) from orders
) as d
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> You should use UNION instead of UNION ALL there.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry my mistake.
Infact you should use UNION ALL instead of UN ION :)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eRIcksQQGHA.5116@.TK2MSFTNGP10.phx.gbl...
> Roji
> No , you won't get an exected result as I understood the OP
> try
> USE northwind
> select sum(ord)
> from
> (
> select sum(orderid) ord from orders
> union --all
> select sum(orderid) from orders
> ) as d
>
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23Hk5POQQGHA.5400@.TK2MSFTNGP09.phx.gbl...
>|||Just for variety, another alternative.
SELECT GrandTotal =
(select sum(ammount) from claimfinancialpayment
where claimid = 10) +
(select sum(ammount) from claimpaymenthistory
where claimid = 10)
Roy Harvey
Beacon Falls, CT
On Mon, 6 Mar 2006 11:38:44 +0200, "Robert Bravery" <me@.u.com> wrote:
>Hi all,
>if I have
>select sum(ammount) from claimfinancialpayment where claimid = 10
>select sum(ammount) from claimpaymenthistory where claimid = 10
>How can I add the two summed values together, when
>select sum(ammount) from claimfinancialpayment where claimid = 10
>sums three rows,
>and
>select sum(ammount) from claimpaymenthistory where claimid = 10
>sums 5 rows
>I was thinking of
>declare @.tot numeric(12,2), @.sum1 numeric(12,2), @.sum2 numeric(12,2)
>select @.sum1 = sum(ammount) from claimfinancialpayment where claimid = 10
>select @.sum2 = sum(ammount) from claimpaymenthistory where claimid = 10
>set @.tot = @.sum1+@.sum2
>Would this be correct or is there an easier or faster way of doing this
>THanks
>RObert
>|||THanks Uri, just what I needed
RObert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uahTBFQQGHA.140@.TK2MSFTNGP12.phx.gbl...
> Robert
> SELECT SUM(bblala)
> FROM
> (
> select sum(ammount) as blbla from claimfinancialpayment where claimid = 10
> UNION ALL
> select sum(ammount) from claimpaymenthistory where claimid = 10
> ) AS Der
>
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:OWgV$BQQGHA.4680@.TK2MSFTNGP10.phx.gbl...
10
>sql
Adding to outputs together to retrieve the top(10) - Is it possible?
Hi There,
I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.
I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.
In simple terms I'm trying to do the following:-
For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.
I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....
I'm currently using to seperate queries as follows :--
--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types
SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
-- I then use the following to get the total of all action scores, again in the 'Volume' column
SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?
Is it possible to output these 2 result to a new table and then join the new tables?
Thanks for any assistance, this is really draining me at the moment..... :-/
here it is,
Select TOP (@.Number)
incident_Data.personnel_code
, incident_Data.personnel_forename
, incident_Data.personnel_Surname
, incident_Data.Volume
, incident_Data.Average
, actions_data.Volume
, actions_data.Average
, incident_Data.Volume + actions_data.Volume as Total_Volume
, incident_Data.Average + actions_data.Average as Total_Average
from
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(incident_types.type_score) as 'Volume'
, avg(incident_types.type_score) as 'Average'
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as incident_Data
Inner Join
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(actions.action_score) as 'Volume'
, AVG(actions.action_score) as 'Average'
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as actions_data
On actions_data.personnel_code = incident_Data.personnel_code
|||
Thank you so much for your reply this has really made my day!
I forgot to mention that the action record is not always present but the incident record is. Therefore I made the actions_data join = 'Left Outer Join' (see code below)... This gives me a problem in that when the action_data record contains 'NULL' the Volume is also 'NULL' - is it possible to make NULL = 0 [zero] when there is no action record?
Thanks again (see code and output).....
[code I've removed some of the selection criteria from previous post for testing purposes]
Select TOP (1000)
incident_Data.personnel_code
, incident_Data.personnel_forename
, incident_Data.personnel_Surname
, incident_Data.Volume
, incident_Data.Average
, actions_data.Volume
, actions_data.Average
, incident_Data.Volume + actions_data.Volume as Total_Volume
, incident_Data.Average + actions_data.Average as Total_Average
from
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(incident_types.type_score) as 'Volume'
, avg(incident_types.type_score) as 'Average'
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
report_basedon.personnel_code <> 0
AND report_header.record_status='1'
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as incident_Data
Left Outer Join
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(actions.action_score) as 'Volume'
, AVG(actions.action_score) as 'Average'
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
personnel_details.personnel_code <> 0
AND report_header.record_status='1'
AND profile.record_status='1'
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as actions_data
On actions_data.personnel_code = incident_Data.personnel_code
Order by incident_data.personnel_code
[example of results]
15 Oscar Freeman 0 0 NULL NULL NULL NULL16 Sofia Daniels 0 0 NULL NULL NULL NULL
19 Megan Hardy 25 12 120 40 145 52
20 Finley Randall -25 -25 NULL NULL NULL NULL|||
observations
1. since actions_data subquery may be missing [i.e. OUTER JOIN] you should use this line instead
, incident_Data.Volume + ISNULL(actions_data.Volume,0) as Total_Volume
, incident_Data.Average + ISNULL(actions_data.Average,0) as Total_Average
2. the Total_Average calculation is flawed, e.g.
4 rows of incident_Data with average of 50 and 1 row of actions_data with average of 20 would yield a Total_Average of 70
when what you probably wanted was 44 (or 35 for simplicity if you don't care about weighting)
Dick
|||Thank you Dick, for both points, your absolutly correct. I was so concerned with not being ale to extract the data that I hadn't thought this though. You've propably saved me a lot more pain!
Just for completeness, I had to go on and resolve a divide by zero error when calculating the final averages etc etc and I found a very useful link http://www.sql-server-helper.com/error-messages/msg-8134.aspx which shows 3 ways of dealing with this error.
Thanks again for everyones help - I can now get on with developing the program!!!!
|||
Yes. ISNULL will fix the issue... If the average is your issue the following query might be a best one..
Not sure why we need a TOP here.. It wont give any performance gain.
Select TOP (@.Number)
personnel_code
, personnel_forename
, personnel_Surname
, sum(case when type='incident' then Score end) as incident_volume
, avg(case when type='incident' then Score end) as incident_average
, sum(case when type='actions' then Score end) as actions_volume
, avg(case when type='actions' then Score end) as actions_average
, sum(Score) as Total_Volume
, avg(Score) as Total_Average
From
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, (incident_types.type_score) as 'Score'
, 'incident' Type
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
UNION ALL
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, (actions.action_score) as 'Score'
, 'actions' Type
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
) as data
Group By
personnel_code
, personnel_forename
, personnel_Surname
Tuesday, March 20, 2012
Adding the sum of column to use as alias
Chumley,
I've double-checked the syntax of the statements I posted, and they're OK.
Have you altered it them any way? Post the exact statement you're executing
and I'll have a look.
Also, which version of SQL Server are you working in?
Thanks
Damien
"Chumley Walrus" wrote:
> Damien, I now get an "Invalid syntax near SUM " error from the sql
> string you have outlined (pointing to the HAVING SUM line). I don't
> understand,a s I know there's data in there meeting this criteria.
>Right,
I'll spare you the 'dynamic sql is bad' stuff, because if you read this
group regularly, you already know.
You are missing a plus sign after your GROUP BY clause, and you cannot use
an alias in your HAVING. In the SQL I posted for you, I put:
SELECT salesperson, SUM( saleamount ) AS allsales
FROM #transactions
--WHERE thedate In ('20050106', '20050206')
GROUP BY salesperson
HAVING SUM( saleamount ) > 0
So you can see, you don't use 'allsales', you use SUM ( saleamount ).
Being rude to Joe is not going to help you. He's earned his right to make
comments like that be being one of the leading authorities in SQL in the
entire world. Even if you don't agree with his point of view, you've at
least got to respect it.
Let me know how you get on with that SQL.
Damien
"Chumley Walrus" wrote:
> I have inner joins (they all work, as the various IDs are related, and
> do fine in my main sql string), I would post the DDL, but I know the
> datatypes are absolutely accurate (saleamount is a money datatype)
> SELECT ticket.Salesperson_ID, employ.LName + ', ' + tblSalesRep.FName
> AS Salesperson, " +
> "ticket.ID, employ.ID, " +
> "transaction.thedate, " +
> "SUM(transactions.saleamount) AS allsales,
> transactions.ticket_ID,transactions.thedate " +
> "FROM ticket " +
> "INNER JOIN employ ON ticket.Salesperson_ID = employ.ID " +
> "INNER JOIN transactions ON ticket.ID=transactions.ticket_ID " +
> "WHERE transactions.theddate IN ('6/1/2005', '6/2/2005')" +
> "GROUP BY Salesperson"
> "HAVING SUM(allsales) > 0 ";
> once again, I get an "Invalid syntax error by SUM" on HAVING
> SUM(allsales) line.
> '
> chumley
>
I've double-checked the syntax of the statements I posted, and they're OK.
Have you altered it them any way? Post the exact statement you're executing
and I'll have a look.
Also, which version of SQL Server are you working in?
Thanks
Damien
"Chumley Walrus" wrote:
> Damien, I now get an "Invalid syntax near SUM " error from the sql
> string you have outlined (pointing to the HAVING SUM line). I don't
> understand,a s I know there's data in there meeting this criteria.
>Right,
I'll spare you the 'dynamic sql is bad' stuff, because if you read this
group regularly, you already know.
You are missing a plus sign after your GROUP BY clause, and you cannot use
an alias in your HAVING. In the SQL I posted for you, I put:
SELECT salesperson, SUM( saleamount ) AS allsales
FROM #transactions
--WHERE thedate In ('20050106', '20050206')
GROUP BY salesperson
HAVING SUM( saleamount ) > 0
So you can see, you don't use 'allsales', you use SUM ( saleamount ).
Being rude to Joe is not going to help you. He's earned his right to make
comments like that be being one of the leading authorities in SQL in the
entire world. Even if you don't agree with his point of view, you've at
least got to respect it.
Let me know how you get on with that SQL.
Damien
"Chumley Walrus" wrote:
> I have inner joins (they all work, as the various IDs are related, and
> do fine in my main sql string), I would post the DDL, but I know the
> datatypes are absolutely accurate (saleamount is a money datatype)
> SELECT ticket.Salesperson_ID, employ.LName + ', ' + tblSalesRep.FName
> AS Salesperson, " +
> "ticket.ID, employ.ID, " +
> "transaction.thedate, " +
> "SUM(transactions.saleamount) AS allsales,
> transactions.ticket_ID,transactions.thedate " +
> "FROM ticket " +
> "INNER JOIN employ ON ticket.Salesperson_ID = employ.ID " +
> "INNER JOIN transactions ON ticket.ID=transactions.ticket_ID " +
> "WHERE transactions.theddate IN ('6/1/2005', '6/2/2005')" +
> "GROUP BY Salesperson"
> "HAVING SUM(allsales) > 0 ";
> once again, I get an "Invalid syntax error by SUM" on HAVING
> SUM(allsales) line.
> '
> chumley
>
Friday, February 24, 2012
Adding Hours, Minutes, Seconds (SQL 2000)
Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000try this tricky thing...
declare @.Dt as datetime
set @.Dt = '2007-02-20'
declare @.Dt1 as datetime
set @.Dt1 = '2007-02-20 01:50:10'
declare @.Dt2 as datetime
set @.Dt2 = '2007-02-20 01:20:05'
select convert(varchar,cast((cast(@.Dt1 as float) - cast(@.Dt as float)) + (cast(@.Dt2 as float) - cast(@.Dt as float)) as datetime),114)
now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)
sum1
----
47:10:15
Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5
JulianDayNo
-------
2454154.9927028548|||I really don't think the formula needs to be that complicated...
set nocount on
declare @.TimeStrings table (TimeString varchar(8))
insert into @.TimeStrings (TimeString) values ('01:50:10')
insert into @.TimeStrings (TimeString) values ('01:20:5')
select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
from @.TimeStrings|||UPalsen's way works - thanx
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000try this tricky thing...
declare @.Dt as datetime
set @.Dt = '2007-02-20'
declare @.Dt1 as datetime
set @.Dt1 = '2007-02-20 01:50:10'
declare @.Dt2 as datetime
set @.Dt2 = '2007-02-20 01:20:05'
select convert(varchar,cast((cast(@.Dt1 as float) - cast(@.Dt as float)) + (cast(@.Dt2 as float) - cast(@.Dt as float)) as datetime),114)
now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)
sum1
----
47:10:15
Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5
JulianDayNo
-------
2454154.9927028548|||I really don't think the formula needs to be that complicated...
set nocount on
declare @.TimeStrings table (TimeString varchar(8))
insert into @.TimeStrings (TimeString) values ('01:50:10')
insert into @.TimeStrings (TimeString) values ('01:20:5')
select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
from @.TimeStrings|||UPalsen's way works - thanx
Sunday, February 19, 2012
Adding Date and zero values to non existent dates
Hi,
I have info about my customers and when they place their orders. I am trying
to get a report that will tell me the sum of their orders for each month fo
r
the last 24 months. The problem I'm having is that certain customers don't
have order in every month so I'm only able to query on what's there.
How can I create a table or a view that would return every months in the
last 24 months with the sum of their orders for each month and 0 for months
that had no orders?
Thanks in advance.Read this for some ideas:
http://www.aspfaq.com/show.asp?id=2519
"Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
> Hi,
> I have info about my customers and when they place their orders. I am
> trying
> to get a report that will tell me the sum of their orders for each month
> for
> the last 24 months. The problem I'm having is that certain customers don't
> have order in every month so I'm only able to query on what's there.
> How can I create a table or a view that would return every months in the
> last 24 months with the sum of their orders for each month and 0 for
> months
> that had no orders?
> Thanks in advance.|||Thanks, I think this will help... Merci Beaucoup!
"Raymond D'Anjou" wrote:
> Read this for some ideas:
> http://www.aspfaq.com/show.asp?id=2519
> "Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
> news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
>
>
I have info about my customers and when they place their orders. I am trying
to get a report that will tell me the sum of their orders for each month fo
r
the last 24 months. The problem I'm having is that certain customers don't
have order in every month so I'm only able to query on what's there.
How can I create a table or a view that would return every months in the
last 24 months with the sum of their orders for each month and 0 for months
that had no orders?
Thanks in advance.Read this for some ideas:
http://www.aspfaq.com/show.asp?id=2519
"Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
> Hi,
> I have info about my customers and when they place their orders. I am
> trying
> to get a report that will tell me the sum of their orders for each month
> for
> the last 24 months. The problem I'm having is that certain customers don't
> have order in every month so I'm only able to query on what's there.
> How can I create a table or a view that would return every months in the
> last 24 months with the sum of their orders for each month and 0 for
> months
> that had no orders?
> Thanks in advance.|||Thanks, I think this will help... Merci Beaucoup!
"Raymond D'Anjou" wrote:
> Read this for some ideas:
> http://www.aspfaq.com/show.asp?id=2519
> "Frenchie418" <Frenchie418@.discussions.microsoft.com> wrote in message
> news:F57924D5-DF9C-46EE-A4B6-B8CFBBBB4026@.microsoft.com...
>
>
Thursday, February 16, 2012
Adding database name at runtime
Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
Adding database name at runtime
Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
Pramod
Hi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegro ups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>
|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegro ups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>
|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pagesbigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegro ups.com...
|||What version of sql server you using..?
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegr oups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>
|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> What version of sql server you using..?
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegr oups.com...
|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegr oups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>
|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegr oups.com...
|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pagesbigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
Pramod
Hi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegro ups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>
|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegro ups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>
|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pagesbigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegro ups.com...
|||What version of sql server you using..?
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegr oups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>
|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> What version of sql server you using..?
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegr oups.com...
|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegr oups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>
|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:[vbcol=seagreen]
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegr oups.com...
|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pagesbigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =
it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =
str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:[vbcol=seagreen]
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
Adding database name at runtime
Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > Hi,
> >
> > I have below SQL query which calculates the database size for all
> > databases.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from dbo.sysfiles
> >
> > But I am not able to substitute the database name which I am getting
> > from the cursor at runtime.
> > I want to place the database name in the following query instead of
> > 'DBNAME'.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from >>DBNAME<<<.dbo.sysfiles
> >
> > Can we replace the 'DBNAME' with the actual database name from the
> > cursor and retrieve the values?
> >
> > Thanks,
> > Regards,
> > Pramod
> >|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>> Hi
>> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
>> ?.dbo.sysfiles'
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I have below SQL query which calculates the database size for all
>> > databases.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from dbo.sysfiles
>> >
>> > But I am not able to substitute the database name which I am getting
>> > from the cursor at runtime.
>> > I want to place the database name in the following query instead of
>> > 'DBNAME'.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from >>DBNAME<<<.dbo.sysfiles
>> >
>> > Can we replace the 'DBNAME' with the actual database name from the
>> > cursor and retrieve the values?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > Thanks for your feedback. It really worked.
> > Now, I have another question.
> >
> > I have a variable @.dbsize to which I am assigning the value of database
> > size and I am using the variable value in the code
> >
> > Below is my SQL query which returns the database free space in percent
> > for all the databases.
> >
> > SET nocount on
> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > master..sysdatabases
> >
> > OPEN AllDatabaseInfo
> >
> > IF object_id('tempdb..#test2') IS NOT NULL
> > BEGIN
> > DROP TABLE #test2
> > END
> >
> > CREATE TABLE #test2 (
> > [Database Name] [varchar] (1000),
> > [Database Space Available] [varchar] (1000)
> > )
> >
> > IF object_id('tempdb..#test3') IS NOT NULL
> > BEGIN
> > DROP TABLE #test3
> > END
> >
> > CREATE TABLE #test3 (
> > [dbsize] [varchar] (1000),
> > [logsize] [varchar] (1000)
> > )
> >
> > DELETE FROM #test2
> > DECLARE @.DBName nvarchar(1000)
> > DECLARE @.sql nvarchar(1000)
> > DECLARE @.str sysname
> > SET @.sql = ''
> > SET @.DBName = ''
> > DECLARE @.pages bigint
> > ,@.dbsize bigint
> > ,@.logsize bigint
> > ,@.reservedpages bigint
> > ,@.unallocatedsize bigint
> > ,@.totalsize bigint
> >
> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > --
> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > 0 end))FROM ?.dbo.sysfiles'
> >
> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > <> 0 then size else 0 end))
> > FROM dbo.sysfiles
> >
> > SELECT @.reservedpages = sum(a.total_pages)
> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > = a.container_id
> > left join sys.internal_tables it on p.object_id = it.object_id
> >
> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > (15,2),@.logsize))/128.00
> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > --
> >
> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > 15,2)
> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > @.str
> > EXEC sp_executesql @.sql
> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > END
> >
> > CLOSE AllDatabaseInfo
> > DEALLOCATE AllDatabaseInfo
> >
> > SELECT * FROM #test2
> > SET nocount off
> >
> >
> >
> > Now this code returns the free space value in percent only for one
> > database because I am unable to substitute the database name when I
> > calculate the @.dbsize.
> >
> > Can you help me?
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > Uri Dimant wrote:
> >> Hi
> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> >> ?.dbo.sysfiles'
> >>
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> > Hi,
> >> >
> >> > I have below SQL query which calculates the database size for all
> >> > databases.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from dbo.sysfiles
> >> >
> >> > But I am not able to substitute the database name which I am getting
> >> > from the cursor at runtime.
> >> > I want to place the database name in the following query instead of
> >> > 'DBNAME'.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >
> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> > cursor and retrieve the values?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>> What version of sql server you using..'
>> vt
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
>> > Hi Uri,
>> >
>> > Thanks for your feedback. It really worked.
>> > Now, I have another question.
>> >
>> > I have a variable @.dbsize to which I am assigning the value of database
>> > size and I am using the variable value in the code
>> >
>> > Below is my SQL query which returns the database free space in percent
>> > for all the databases.
>> >
>> > SET nocount on
>> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
>> > master..sysdatabases
>> >
>> > OPEN AllDatabaseInfo
>> >
>> > IF object_id('tempdb..#test2') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test2
>> > END
>> >
>> > CREATE TABLE #test2 (
>> > [Database Name] [varchar] (1000),
>> > [Database Space Available] [varchar] (1000)
>> > )
>> >
>> > IF object_id('tempdb..#test3') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test3
>> > END
>> >
>> > CREATE TABLE #test3 (
>> > [dbsize] [varchar] (1000),
>> > [logsize] [varchar] (1000)
>> > )
>> >
>> > DELETE FROM #test2
>> > DECLARE @.DBName nvarchar(1000)
>> > DECLARE @.sql nvarchar(1000)
>> > DECLARE @.str sysname
>> > SET @.sql = ''
>> > SET @.DBName = ''
>> > DECLARE @.pages bigint
>> > ,@.dbsize bigint
>> > ,@.logsize bigint
>> > ,@.reservedpages bigint
>> > ,@.unallocatedsize bigint
>> > ,@.totalsize bigint
>> >
>> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN
>> > --
>> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =>> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
>> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
>> > 0 end))FROM ?.dbo.sysfiles'
>> >
>> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
>> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
>> > <> 0 then size else 0 end))
>> > FROM dbo.sysfiles
>> >
>> > SELECT @.reservedpages = sum(a.total_pages)
>> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
>> > = a.container_id
>> > left join sys.internal_tables it on p.object_id = it.object_id
>> >
>> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
>> > (15,2),@.logsize))/128.00
>> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
>> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
>> > --
>> >
>> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
>> > 15,2)
>> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
>> > @.str
>> > EXEC sp_executesql @.sql
>> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
>> > END
>> >
>> > CLOSE AllDatabaseInfo
>> > DEALLOCATE AllDatabaseInfo
>> >
>> > SELECT * FROM #test2
>> > SET nocount off
>> >
>> >
>> >
>> > Now this code returns the free space value in percent only for one
>> > database because I am unable to substitute the database name when I
>> > calculate the @.dbsize.
>> >
>> > Can you help me?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>> > Uri Dimant wrote:
>> >> Hi
>> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> >> end))FROM
>> >> ?.dbo.sysfiles'
>> >>
>> >>
>> >>
>> >> <ipramod@.gmail.com> wrote in message
>> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> >> > Hi,
>> >> >
>> >> > I have below SQL query which calculates the database size for all
>> >> > databases.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from dbo.sysfiles
>> >> >
>> >> > But I am not able to substitute the database name which I am getting
>> >> > from the cursor at runtime.
>> >> > I want to place the database name in the following query instead of
>> >> > 'DBNAME'.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from >>DBNAME<<<.dbo.sysfiles
>> >> >
>> >> > Can we replace the 'DBNAME' with the actual database name from the
>> >> > cursor and retrieve the values?
>> >> >
>> >> > Thanks,
>> >> > Regards,
>> >> > Pramod
>> >> >
>> >
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > SQL Server 2005 RTM Version
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > vt wrote:
> >> What version of sql server you using..'
> >>
> >> vt
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> >> > Hi Uri,
> >> >
> >> > Thanks for your feedback. It really worked.
> >> > Now, I have another question.
> >> >
> >> > I have a variable @.dbsize to which I am assigning the value of database
> >> > size and I am using the variable value in the code
> >> >
> >> > Below is my SQL query which returns the database free space in percent
> >> > for all the databases.
> >> >
> >> > SET nocount on
> >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> >> > master..sysdatabases
> >> >
> >> > OPEN AllDatabaseInfo
> >> >
> >> > IF object_id('tempdb..#test2') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test2
> >> > END
> >> >
> >> > CREATE TABLE #test2 (
> >> > [Database Name] [varchar] (1000),
> >> > [Database Space Available] [varchar] (1000)
> >> > )
> >> >
> >> > IF object_id('tempdb..#test3') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test3
> >> > END
> >> >
> >> > CREATE TABLE #test3 (
> >> > [dbsize] [varchar] (1000),
> >> > [logsize] [varchar] (1000)
> >> > )
> >> >
> >> > DELETE FROM #test2
> >> > DECLARE @.DBName nvarchar(1000)
> >> > DECLARE @.sql nvarchar(1000)
> >> > DECLARE @.str sysname
> >> > SET @.sql = ''
> >> > SET @.DBName = ''
> >> > DECLARE @.pages bigint
> >> > ,@.dbsize bigint
> >> > ,@.logsize bigint
> >> > ,@.reservedpages bigint
> >> > ,@.unallocatedsize bigint
> >> > ,@.totalsize bigint
> >> >
> >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> >> > WHILE @.@.FETCH_STATUS = 0
> >> > BEGIN
> >> > --
> >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> >> > 0 end))FROM ?.dbo.sysfiles'
> >> >
> >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> >> > <> 0 then size else 0 end))
> >> > FROM dbo.sysfiles
> >> >
> >> > SELECT @.reservedpages = sum(a.total_pages)
> >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> >> > = a.container_id
> >> > left join sys.internal_tables it on p.object_id = it.object_id
> >> >
> >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> >> > (15,2),@.logsize))/128.00
> >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> >> > --
> >> >
> >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> >> > 15,2)
> >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> >> > @.str
> >> > EXEC sp_executesql @.sql
> >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> >> > END
> >> >
> >> > CLOSE AllDatabaseInfo
> >> > DEALLOCATE AllDatabaseInfo
> >> >
> >> > SELECT * FROM #test2
> >> > SET nocount off
> >> >
> >> >
> >> >
> >> > Now this code returns the free space value in percent only for one
> >> > database because I am unable to substitute the database name when I
> >> > calculate the @.dbsize.
> >> >
> >> > Can you help me?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >> > Uri Dimant wrote:
> >> >> Hi
> >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> >> end))FROM
> >> >> ?.dbo.sysfiles'
> >> >>
> >> >>
> >> >>
> >> >> <ipramod@.gmail.com> wrote in message
> >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> >> > Hi,
> >> >> >
> >> >> > I have below SQL query which calculates the database size for all
> >> >> > databases.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from dbo.sysfiles
> >> >> >
> >> >> > But I am not able to substitute the database name which I am getting
> >> >> > from the cursor at runtime.
> >> >> > I want to place the database name in the following query instead of
> >> >> > 'DBNAME'.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >> >
> >> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> >> > cursor and retrieve the values?
> >> >> >
> >> >> > Thanks,
> >> >> > Regards,
> >> >> > Pramod
> >> >> >
> >> >
> >|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys :)
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
> > Sorry buddy.. still using 2000
> >
> >
> >
> > <ipramod@.gmail.com> wrote in message
> > news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > > SQL Server 2005 RTM Version
> > >
> > > Thanks,
> > > Regards,
> > > Pramod
> > >
> > > vt wrote:
> > >> What version of sql server you using..'
> > >>
> > >> vt
> > >>
> > >>
> > >> <ipramod@.gmail.com> wrote in message
> > >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > >> > Hi Uri,
> > >> >
> > >> > Thanks for your feedback. It really worked.
> > >> > Now, I have another question.
> > >> >
> > >> > I have a variable @.dbsize to which I am assigning the value of database
> > >> > size and I am using the variable value in the code
> > >> >
> > >> > Below is my SQL query which returns the database free space in percent
> > >> > for all the databases.
> > >> >
> > >> > SET nocount on
> > >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > >> > master..sysdatabases
> > >> >
> > >> > OPEN AllDatabaseInfo
> > >> >
> > >> > IF object_id('tempdb..#test2') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test2
> > >> > END
> > >> >
> > >> > CREATE TABLE #test2 (
> > >> > [Database Name] [varchar] (1000),
> > >> > [Database Space Available] [varchar] (1000)
> > >> > )
> > >> >
> > >> > IF object_id('tempdb..#test3') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test3
> > >> > END
> > >> >
> > >> > CREATE TABLE #test3 (
> > >> > [dbsize] [varchar] (1000),
> > >> > [logsize] [varchar] (1000)
> > >> > )
> > >> >
> > >> > DELETE FROM #test2
> > >> > DECLARE @.DBName nvarchar(1000)
> > >> > DECLARE @.sql nvarchar(1000)
> > >> > DECLARE @.str sysname
> > >> > SET @.sql = ''
> > >> > SET @.DBName = ''
> > >> > DECLARE @.pages bigint
> > >> > ,@.dbsize bigint
> > >> > ,@.logsize bigint
> > >> > ,@.reservedpages bigint
> > >> > ,@.unallocatedsize bigint
> > >> > ,@.totalsize bigint
> > >> >
> > >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > >> > WHILE @.@.FETCH_STATUS = 0
> > >> > BEGIN
> > >> > --
> > >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> > 0 end))FROM ?.dbo.sysfiles'
> > >> >
> > >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > >> > <> 0 then size else 0 end))
> > >> > FROM dbo.sysfiles
> > >> >
> > >> > SELECT @.reservedpages = sum(a.total_pages)
> > >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > >> > = a.container_id
> > >> > left join sys.internal_tables it on p.object_id = it.object_id
> > >> >
> > >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > >> > (15,2),@.logsize))/128.00
> > >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > >> > --
> > >> >
> > >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > >> > 15,2)
> > >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > >> > @.str
> > >> > EXEC sp_executesql @.sql
> > >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > >> > END
> > >> >
> > >> > CLOSE AllDatabaseInfo
> > >> > DEALLOCATE AllDatabaseInfo
> > >> >
> > >> > SELECT * FROM #test2
> > >> > SET nocount off
> > >> >
> > >> >
> > >> >
> > >> > Now this code returns the free space value in percent only for one
> > >> > database because I am unable to substitute the database name when I
> > >> > calculate the @.dbsize.
> > >> >
> > >> > Can you help me?
> > >> >
> > >> > Thanks,
> > >> > Regards,
> > >> > Pramod
> > >> >
> > >> > Uri Dimant wrote:
> > >> >> Hi
> > >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> > >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> > >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > >> >> end))FROM
> > >> >> ?.dbo.sysfiles'
> > >> >>
> > >> >>
> > >> >>
> > >> >> <ipramod@.gmail.com> wrote in message
> > >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > >> >> > Hi,
> > >> >> >
> > >> >> > I have below SQL query which calculates the database size for all
> > >> >> > databases.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from dbo.sysfiles
> > >> >> >
> > >> >> > But I am not able to substitute the database name which I am getting
> > >> >> > from the cursor at runtime.
> > >> >> > I want to place the database name in the following query instead of
> > >> >> > 'DBNAME'.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from >>DBNAME<<<.dbo.sysfiles
> > >> >> >
> > >> >> > Can we replace the 'DBNAME' with the actual database name from the
> > >> >> > cursor and retrieve the values?
> > >> >> >
> > >> >> > Thanks,
> > >> >> > Regards,
> > >> >> > Pramod
> > >> >> >
> > >> >
> > >
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from dbo.sysfiles
But I am not able to substitute the database name which I am getting
from the cursor at runtime.
I want to place the database name in the following query instead of
'DBNAME'.
select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
end))
from >>DBNAME<<<.dbo.sysfiles
Can we replace the 'DBNAME' with the actual database name from the
cursor and retrieve the values?
Thanks,
Regards,
PramodHi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
?.dbo.sysfiles'
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||try this
declare @.dbname varchar(10)
set @.dbname='Northwind'
exec('select sum(convert(bigint,case when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from['+ @.dbname +'].[dbo].[sysfiles]')
Vt
<ipramod@.gmail.com> wrote in message
news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> Hi,
> I have below SQL query which calculates the database size for all
> databases.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from dbo.sysfiles
> But I am not able to substitute the database name which I am getting
> from the cursor at runtime.
> I want to place the database name in the following query instead of
> 'DBNAME'.
> select sum(convert(bigint,case when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> end))
> from >>DBNAME<<<.dbo.sysfiles
> Can we replace the 'DBNAME' with the actual database name from the
> cursor and retrieve the values?
> Thanks,
> Regards,
> Pramod
>|||Hi Uri,
Thanks for your feedback. It really worked.
Now, I have another question.
I have a variable @.dbsize to which I am assigning the value of database
size and I am using the variable value in the code
Below is my SQL query which returns the database free space in percent
for all the databases.
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[dbsize] [varchar] (1000),
[logsize] [varchar] (1000)
)
DELETE FROM #test2
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize bigint
,@.totalsize bigint
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
--
--EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM dbo.sysfiles
SELECT @.reservedpages = sum(a.total_pages)
FROM sys.partitions p join sys.allocation_units a on p.partition_id
= a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
(15,2),@.logsize))/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
(dec (15,2),@.reservedpages)) * 8192 / 1048576
--
SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Now this code returns the free space value in percent only for one
database because I am unable to substitute the database name when I
calculate the @.dbsize.
Can you help me?
Thanks,
Regards,
Pramod
Uri Dimant wrote:
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> ?.dbo.sysfiles'
>
> <ipramod@.gmail.com> wrote in message
> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > Hi,
> >
> > I have below SQL query which calculates the database size for all
> > databases.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from dbo.sysfiles
> >
> > But I am not able to substitute the database name which I am getting
> > from the cursor at runtime.
> > I want to place the database name in the following query instead of
> > 'DBNAME'.
> >
> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > end))
> > from >>DBNAME<<<.dbo.sysfiles
> >
> > Can we replace the 'DBNAME' with the actual database name from the
> > cursor and retrieve the values?
> >
> > Thanks,
> > Regards,
> > Pramod
> >|||What version of sql server you using..'
vt
<ipramod@.gmail.com> wrote in message
news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> Hi Uri,
> Thanks for your feedback. It really worked.
> Now, I have another question.
> I have a variable @.dbsize to which I am assigning the value of database
> size and I am using the variable value in the code
> Below is my SQL query which returns the database free space in percent
> for all the databases.
> SET nocount on
> DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> master..sysdatabases
> OPEN AllDatabaseInfo
> IF object_id('tempdb..#test2') IS NOT NULL
> BEGIN
> DROP TABLE #test2
> END
> CREATE TABLE #test2 (
> [Database Name] [varchar] (1000),
> [Database Space Available] [varchar] (1000)
> )
> IF object_id('tempdb..#test3') IS NOT NULL
> BEGIN
> DROP TABLE #test3
> END
> CREATE TABLE #test3 (
> [dbsize] [varchar] (1000),
> [logsize] [varchar] (1000)
> )
> DELETE FROM #test2
> DECLARE @.DBName nvarchar(1000)
> DECLARE @.sql nvarchar(1000)
> DECLARE @.str sysname
> SET @.sql = ''
> SET @.DBName = ''
> DECLARE @.pages bigint
> ,@.dbsize bigint
> ,@.logsize bigint
> ,@.reservedpages bigint
> ,@.unallocatedsize bigint
> ,@.totalsize bigint
> FETCH NEXT FROM AllDatabaseInfo into @.DBName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --
> --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
> SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> <> 0 then size else 0 end))
> FROM dbo.sysfiles
> SELECT @.reservedpages = sum(a.total_pages)
> FROM sys.partitions p join sys.allocation_units a on p.partition_id
> = a.container_id
> left join sys.internal_tables it on p.object_id = it.object_id
> SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> (15,2),@.logsize))/128.00
> SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> (dec (15,2),@.reservedpages)) * 8192 / 1048576
> --
> SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> 15,2)
> SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> @.str
> EXEC sp_executesql @.sql
> FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> END
> CLOSE AllDatabaseInfo
> DEALLOCATE AllDatabaseInfo
> SELECT * FROM #test2
> SET nocount off
>
> Now this code returns the free space value in percent only for one
> database because I am unable to substitute the database name when I
> calculate the @.dbsize.
> Can you help me?
> Thanks,
> Regards,
> Pramod
> Uri Dimant wrote:
>> Hi
>> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
>> ?.dbo.sysfiles'
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I have below SQL query which calculates the database size for all
>> > databases.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from dbo.sysfiles
>> >
>> > But I am not able to substitute the database name which I am getting
>> > from the cursor at runtime.
>> > I want to place the database name in the following query instead of
>> > 'DBNAME'.
>> >
>> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> > end))
>> > from >>DBNAME<<<.dbo.sysfiles
>> >
>> > Can we replace the 'DBNAME' with the actual database name from the
>> > cursor and retrieve the values?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>|||SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:
> What version of sql server you using..'
> vt
>
> <ipramod@.gmail.com> wrote in message
> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > Thanks for your feedback. It really worked.
> > Now, I have another question.
> >
> > I have a variable @.dbsize to which I am assigning the value of database
> > size and I am using the variable value in the code
> >
> > Below is my SQL query which returns the database free space in percent
> > for all the databases.
> >
> > SET nocount on
> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > master..sysdatabases
> >
> > OPEN AllDatabaseInfo
> >
> > IF object_id('tempdb..#test2') IS NOT NULL
> > BEGIN
> > DROP TABLE #test2
> > END
> >
> > CREATE TABLE #test2 (
> > [Database Name] [varchar] (1000),
> > [Database Space Available] [varchar] (1000)
> > )
> >
> > IF object_id('tempdb..#test3') IS NOT NULL
> > BEGIN
> > DROP TABLE #test3
> > END
> >
> > CREATE TABLE #test3 (
> > [dbsize] [varchar] (1000),
> > [logsize] [varchar] (1000)
> > )
> >
> > DELETE FROM #test2
> > DECLARE @.DBName nvarchar(1000)
> > DECLARE @.sql nvarchar(1000)
> > DECLARE @.str sysname
> > SET @.sql = ''
> > SET @.DBName = ''
> > DECLARE @.pages bigint
> > ,@.dbsize bigint
> > ,@.logsize bigint
> > ,@.reservedpages bigint
> > ,@.unallocatedsize bigint
> > ,@.totalsize bigint
> >
> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > --
> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > 0 end))FROM ?.dbo.sysfiles'
> >
> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > <> 0 then size else 0 end))
> > FROM dbo.sysfiles
> >
> > SELECT @.reservedpages = sum(a.total_pages)
> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > = a.container_id
> > left join sys.internal_tables it on p.object_id = it.object_id
> >
> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > (15,2),@.logsize))/128.00
> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > --
> >
> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > 15,2)
> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > @.str
> > EXEC sp_executesql @.sql
> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > END
> >
> > CLOSE AllDatabaseInfo
> > DEALLOCATE AllDatabaseInfo
> >
> > SELECT * FROM #test2
> > SET nocount off
> >
> >
> >
> > Now this code returns the free space value in percent only for one
> > database because I am unable to substitute the database name when I
> > calculate the @.dbsize.
> >
> > Can you help me?
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > Uri Dimant wrote:
> >> Hi
> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))FROM
> >> ?.dbo.sysfiles'
> >>
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> > Hi,
> >> >
> >> > I have below SQL query which calculates the database size for all
> >> > databases.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from dbo.sysfiles
> >> >
> >> > But I am not able to substitute the database name which I am getting
> >> > from the cursor at runtime.
> >> > I want to place the database name in the following query instead of
> >> > 'DBNAME'.
> >> >
> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> > end))
> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >
> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> > cursor and retrieve the values?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >|||Sorry buddy.. still using 2000
<ipramod@.gmail.com> wrote in message
news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> SQL Server 2005 RTM Version
> Thanks,
> Regards,
> Pramod
> vt wrote:
>> What version of sql server you using..'
>> vt
>>
>> <ipramod@.gmail.com> wrote in message
>> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
>> > Hi Uri,
>> >
>> > Thanks for your feedback. It really worked.
>> > Now, I have another question.
>> >
>> > I have a variable @.dbsize to which I am assigning the value of database
>> > size and I am using the variable value in the code
>> >
>> > Below is my SQL query which returns the database free space in percent
>> > for all the databases.
>> >
>> > SET nocount on
>> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
>> > master..sysdatabases
>> >
>> > OPEN AllDatabaseInfo
>> >
>> > IF object_id('tempdb..#test2') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test2
>> > END
>> >
>> > CREATE TABLE #test2 (
>> > [Database Name] [varchar] (1000),
>> > [Database Space Available] [varchar] (1000)
>> > )
>> >
>> > IF object_id('tempdb..#test3') IS NOT NULL
>> > BEGIN
>> > DROP TABLE #test3
>> > END
>> >
>> > CREATE TABLE #test3 (
>> > [dbsize] [varchar] (1000),
>> > [logsize] [varchar] (1000)
>> > )
>> >
>> > DELETE FROM #test2
>> > DECLARE @.DBName nvarchar(1000)
>> > DECLARE @.sql nvarchar(1000)
>> > DECLARE @.str sysname
>> > SET @.sql = ''
>> > SET @.DBName = ''
>> > DECLARE @.pages bigint
>> > ,@.dbsize bigint
>> > ,@.logsize bigint
>> > ,@.reservedpages bigint
>> > ,@.unallocatedsize bigint
>> > ,@.totalsize bigint
>> >
>> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
>> > WHILE @.@.FETCH_STATUS = 0
>> > BEGIN
>> > --
>> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize =>> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
>> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
>> > 0 end))FROM ?.dbo.sysfiles'
>> >
>> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
>> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
>> > <> 0 then size else 0 end))
>> > FROM dbo.sysfiles
>> >
>> > SELECT @.reservedpages = sum(a.total_pages)
>> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
>> > = a.container_id
>> > left join sys.internal_tables it on p.object_id = it.object_id
>> >
>> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
>> > (15,2),@.logsize))/128.00
>> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
>> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
>> > --
>> >
>> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
>> > 15,2)
>> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
>> > @.str
>> > EXEC sp_executesql @.sql
>> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
>> > END
>> >
>> > CLOSE AllDatabaseInfo
>> > DEALLOCATE AllDatabaseInfo
>> >
>> > SELECT * FROM #test2
>> > SET nocount off
>> >
>> >
>> >
>> > Now this code returns the free space value in percent only for one
>> > database because I am unable to substitute the database name when I
>> > calculate the @.dbsize.
>> >
>> > Can you help me?
>> >
>> > Thanks,
>> > Regards,
>> > Pramod
>> >
>> > Uri Dimant wrote:
>> >> Hi
>> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
>> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
>> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
>> >> end))FROM
>> >> ?.dbo.sysfiles'
>> >>
>> >>
>> >>
>> >> <ipramod@.gmail.com> wrote in message
>> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
>> >> > Hi,
>> >> >
>> >> > I have below SQL query which calculates the database size for all
>> >> > databases.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from dbo.sysfiles
>> >> >
>> >> > But I am not able to substitute the database name which I am getting
>> >> > from the cursor at runtime.
>> >> > I want to place the database name in the following query instead of
>> >> > 'DBNAME'.
>> >> >
>> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
>> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
>> >> > 0
>> >> > end))
>> >> > from >>DBNAME<<<.dbo.sysfiles
>> >> >
>> >> > Can we replace the 'DBNAME' with the actual database name from the
>> >> > cursor and retrieve the values?
>> >> >
>> >> > Thanks,
>> >> > Regards,
>> >> > Pramod
>> >> >
>> >
>|||Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:
> Sorry buddy.. still using 2000
>
> <ipramod@.gmail.com> wrote in message
> news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > SQL Server 2005 RTM Version
> >
> > Thanks,
> > Regards,
> > Pramod
> >
> > vt wrote:
> >> What version of sql server you using..'
> >>
> >> vt
> >>
> >>
> >> <ipramod@.gmail.com> wrote in message
> >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> >> > Hi Uri,
> >> >
> >> > Thanks for your feedback. It really worked.
> >> > Now, I have another question.
> >> >
> >> > I have a variable @.dbsize to which I am assigning the value of database
> >> > size and I am using the variable value in the code
> >> >
> >> > Below is my SQL query which returns the database free space in percent
> >> > for all the databases.
> >> >
> >> > SET nocount on
> >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> >> > master..sysdatabases
> >> >
> >> > OPEN AllDatabaseInfo
> >> >
> >> > IF object_id('tempdb..#test2') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test2
> >> > END
> >> >
> >> > CREATE TABLE #test2 (
> >> > [Database Name] [varchar] (1000),
> >> > [Database Space Available] [varchar] (1000)
> >> > )
> >> >
> >> > IF object_id('tempdb..#test3') IS NOT NULL
> >> > BEGIN
> >> > DROP TABLE #test3
> >> > END
> >> >
> >> > CREATE TABLE #test3 (
> >> > [dbsize] [varchar] (1000),
> >> > [logsize] [varchar] (1000)
> >> > )
> >> >
> >> > DELETE FROM #test2
> >> > DECLARE @.DBName nvarchar(1000)
> >> > DECLARE @.sql nvarchar(1000)
> >> > DECLARE @.str sysname
> >> > SET @.sql = ''
> >> > SET @.DBName = ''
> >> > DECLARE @.pages bigint
> >> > ,@.dbsize bigint
> >> > ,@.logsize bigint
> >> > ,@.reservedpages bigint
> >> > ,@.unallocatedsize bigint
> >> > ,@.totalsize bigint
> >> >
> >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> >> > WHILE @.@.FETCH_STATUS = 0
> >> > BEGIN
> >> > --
> >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> >> > 0 end))FROM ?.dbo.sysfiles'
> >> >
> >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> >> > <> 0 then size else 0 end))
> >> > FROM dbo.sysfiles
> >> >
> >> > SELECT @.reservedpages = sum(a.total_pages)
> >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> >> > = a.container_id
> >> > left join sys.internal_tables it on p.object_id = it.object_id
> >> >
> >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> >> > (15,2),@.logsize))/128.00
> >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> >> > --
> >> >
> >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> >> > 15,2)
> >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> >> > @.str
> >> > EXEC sp_executesql @.sql
> >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> >> > END
> >> >
> >> > CLOSE AllDatabaseInfo
> >> > DEALLOCATE AllDatabaseInfo
> >> >
> >> > SELECT * FROM #test2
> >> > SET nocount off
> >> >
> >> >
> >> >
> >> > Now this code returns the free space value in percent only for one
> >> > database because I am unable to substitute the database name when I
> >> > calculate the @.dbsize.
> >> >
> >> > Can you help me?
> >> >
> >> > Thanks,
> >> > Regards,
> >> > Pramod
> >> >
> >> > Uri Dimant wrote:
> >> >> Hi
> >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> >> >> end))FROM
> >> >> ?.dbo.sysfiles'
> >> >>
> >> >>
> >> >>
> >> >> <ipramod@.gmail.com> wrote in message
> >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> >> >> > Hi,
> >> >> >
> >> >> > I have below SQL query which calculates the database size for all
> >> >> > databases.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from dbo.sysfiles
> >> >> >
> >> >> > But I am not able to substitute the database name which I am getting
> >> >> > from the cursor at runtime.
> >> >> > I want to place the database name in the following query instead of
> >> >> > 'DBNAME'.
> >> >> >
> >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> >> >> > 0
> >> >> > end))
> >> >> > from >>DBNAME<<<.dbo.sysfiles
> >> >> >
> >> >> > Can we replace the 'DBNAME' with the actual database name from the
> >> >> > cursor and retrieve the values?
> >> >> >
> >> >> > Thanks,
> >> >> > Regards,
> >> >> > Pramod
> >> >> >
> >> >
> >|||Hi Vt,
I have sorted out the issue by using the temporary tables. I have used
your suggestion and in the 'exec' itself I have inserted the variable
values in the temporary table and it worked. Thanks for your feedback
guys :)
I am copying the solution here, plz take a look and let me know if I am
wrong and if possible give me another solution. Also, can you tell me
is there any disadvantages of having temp tables in the query?
SET nocount on
DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
master..sysdatabases
OPEN AllDatabaseInfo
IF object_id('tempdb..#test2') IS NOT NULL
BEGIN
DROP TABLE #test2
END
CREATE TABLE #test2 (
[Database Name] [varchar] (1000),
[Database Space Available] [varchar] (1000)
)
DELETE FROM #test2
IF object_id('tempdb..#test3') IS NOT NULL
BEGIN
DROP TABLE #test3
END
CREATE TABLE #test3 (
[DatabaseSize] [bigint],
[LogSize] [bigint]
)
DELETE FROM #test3
DECLARE @.DBName nvarchar(1000)
DECLARE @.sql nvarchar(1000)
DECLARE @.str sysname
SET @.sql = ''
SET @.DBName = ''
DECLARE @.pages bigint
,@.dbsize bigint
,@.logsize bigint
,@.reservedpages bigint
,@.unallocatedsize float
,@.totalsize float
FETCH NEXT FROM AllDatabaseInfo into @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sql = N'DECLARE @.dbsize1 bigint,@.logsize1 bigint;
SELECT @.dbsize1 = sum(convert(bigint,case when status & 64 = 0 then
size else 0 end)), @.logsize1 = sum(convert(bigint,case when status & 64
<> 0 then size else 0 end))
FROM ['+ @.DBname +'].dbo.sysfiles;
INSERT INTO #test3 SELECT @.dbsize1, @.logsize1;'
EXEC sp_executesql @.sql
SELECT @.dbsize=[DatabaseSize], @.logsize=[LogSize] FROM #test3
SET @.sql = N'DECLARE @.reservedpages1 bigint;
SELECT @.reservedpages1 = sum(a.total_pages)
FROM ['+ @.DBname +'].sys.partitions p join ['+ @.DBname
+'].sys.allocation_units a on p.partition_id = a.container_id
left join ['+ @.DBname +'].sys.internal_tables it on p.object_id =it.object_id;
INSERT INTO #test3 SELECT @.reservedpages1, 0;'
EXEC sp_executesql @.sql
SELECT @.reservedpages=[DatabaseSize] FROM #test3
SELECT @.totalsize=(convert (dec (15,2),@.dbsize)*1.00 + convert (dec
(15,2),@.logsize))*1.00/128.00
SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize)*1.00 -
convert (dec (15,2),@.reservedpages)*1.00) * 8192.00 / 1048576.00
SET @.str =str((@.unallocatedsize*1.00/@.totalsize*1.00)*100.00, 15,2)
SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
@.str
EXEC sp_executesql @.sql
FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
END
CLOSE AllDatabaseInfo
DEALLOCATE AllDatabaseInfo
SELECT * FROM #test2
SET nocount off
Thanks,
Pramod
ipramod@.gmail.com wrote:
> Hi Vt,
> I have tried the same with SQL Server 2000 also, but it is not working.
> Regards,
> Pramod
> vt wrote:
> > Sorry buddy.. still using 2000
> >
> >
> >
> > <ipramod@.gmail.com> wrote in message
> > news:1163082808.701906.266480@.f16g2000cwb.googlegroups.com...
> > > SQL Server 2005 RTM Version
> > >
> > > Thanks,
> > > Regards,
> > > Pramod
> > >
> > > vt wrote:
> > >> What version of sql server you using..'
> > >>
> > >> vt
> > >>
> > >>
> > >> <ipramod@.gmail.com> wrote in message
> > >> news:1163074984.662784.299320@.h48g2000cwc.googlegroups.com...
> > >> > Hi Uri,
> > >> >
> > >> > Thanks for your feedback. It really worked.
> > >> > Now, I have another question.
> > >> >
> > >> > I have a variable @.dbsize to which I am assigning the value of database
> > >> > size and I am using the variable value in the code
> > >> >
> > >> > Below is my SQL query which returns the database free space in percent
> > >> > for all the databases.
> > >> >
> > >> > SET nocount on
> > >> > DECLARE AllDatabaseInfo CURSOR LOCAL FOR SELECT name FROM
> > >> > master..sysdatabases
> > >> >
> > >> > OPEN AllDatabaseInfo
> > >> >
> > >> > IF object_id('tempdb..#test2') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test2
> > >> > END
> > >> >
> > >> > CREATE TABLE #test2 (
> > >> > [Database Name] [varchar] (1000),
> > >> > [Database Space Available] [varchar] (1000)
> > >> > )
> > >> >
> > >> > IF object_id('tempdb..#test3') IS NOT NULL
> > >> > BEGIN
> > >> > DROP TABLE #test3
> > >> > END
> > >> >
> > >> > CREATE TABLE #test3 (
> > >> > [dbsize] [varchar] (1000),
> > >> > [logsize] [varchar] (1000)
> > >> > )
> > >> >
> > >> > DELETE FROM #test2
> > >> > DECLARE @.DBName nvarchar(1000)
> > >> > DECLARE @.sql nvarchar(1000)
> > >> > DECLARE @.str sysname
> > >> > SET @.sql = ''
> > >> > SET @.DBName = ''
> > >> > DECLARE @.pages bigint
> > >> > ,@.dbsize bigint
> > >> > ,@.logsize bigint
> > >> > ,@.reservedpages bigint
> > >> > ,@.unallocatedsize bigint
> > >> > ,@.totalsize bigint
> > >> >
> > >> > FETCH NEXT FROM AllDatabaseInfo into @.DBName
> > >> > WHILE @.@.FETCH_STATUS = 0
> > >> > BEGIN
> > >> > --
> > >> > --EXEC sp_MSForeachdb 'use [?]; select db_name();select @.dbsize => > >> > sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
> > >> > @.logsize = sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> > 0 end))FROM ?.dbo.sysfiles'
> > >> >
> > >> > SELECT @.dbsize = sum(convert(bigint,case when status & 64 = 0 then
> > >> > size else 0 end)), @.logsize = sum(convert(bigint,case when status & 64
> > >> > <> 0 then size else 0 end))
> > >> > FROM dbo.sysfiles
> > >> >
> > >> > SELECT @.reservedpages = sum(a.total_pages)
> > >> > FROM sys.partitions p join sys.allocation_units a on p.partition_id
> > >> > = a.container_id
> > >> > left join sys.internal_tables it on p.object_id = it.object_id
> > >> >
> > >> > SELECT @.totalsize=(convert (dec (15,2),@.dbsize) + convert (dec
> > >> > (15,2),@.logsize))/128.00
> > >> > SELECT @.unallocatedsize=(convert (dec (15,2),@.dbsize) - convert
> > >> > (dec (15,2),@.reservedpages)) * 8192 / 1048576
> > >> > --
> > >> >
> > >> > SET @.str = str((@.unallocatedsize*1.00/@.totalsize)*100.00,
> > >> > 15,2)
> > >> > SET @.sql = N'INSERT INTO #test2 SELECT ''' + @.DBName + ''', ' +
> > >> > @.str
> > >> > EXEC sp_executesql @.sql
> > >> > FETCH NEXT FROM AllDatabaseInfo INTO @.DBName
> > >> > END
> > >> >
> > >> > CLOSE AllDatabaseInfo
> > >> > DEALLOCATE AllDatabaseInfo
> > >> >
> > >> > SELECT * FROM #test2
> > >> > SET nocount off
> > >> >
> > >> >
> > >> >
> > >> > Now this code returns the free space value in percent only for one
> > >> > database because I am unable to substitute the database name when I
> > >> > calculate the @.dbsize.
> > >> >
> > >> > Can you help me?
> > >> >
> > >> > Thanks,
> > >> > Regards,
> > >> > Pramod
> > >> >
> > >> > Uri Dimant wrote:
> > >> >> Hi
> > >> >> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> > >> >> sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) +
> > >> >> sum(convert(bigint,case when status & 64 <> 0 then size else 0
> > >> >> end))FROM
> > >> >> ?.dbo.sysfiles'
> > >> >>
> > >> >>
> > >> >>
> > >> >> <ipramod@.gmail.com> wrote in message
> > >> >> news:1163072983.092041.71650@.f16g2000cwb.googlegroups.com...
> > >> >> > Hi,
> > >> >> >
> > >> >> > I have below SQL query which calculates the database size for all
> > >> >> > databases.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from dbo.sysfiles
> > >> >> >
> > >> >> > But I am not able to substitute the database name which I am getting
> > >> >> > from the cursor at runtime.
> > >> >> > I want to place the database name in the following query instead of
> > >> >> > 'DBNAME'.
> > >> >> >
> > >> >> > select sum(convert(bigint,case when status & 64 = 0 then size else 0
> > >> >> > end)) + sum(convert(bigint,case when status & 64 <> 0 then size else
> > >> >> > 0
> > >> >> > end))
> > >> >> > from >>DBNAME<<<.dbo.sysfiles
> > >> >> >
> > >> >> > Can we replace the 'DBNAME' with the actual database name from the
> > >> >> > cursor and retrieve the values?
> > >> >> >
> > >> >> > Thanks,
> > >> >> > Regards,
> > >> >> > Pramod
> > >> >> >
> > >> >
> > >
Subscribe to:
Posts (Atom)