Sql Activity log backup: bigint

Showing posts with label bigint. Show all posts

Monday, March 19, 2012

adding sql values from 2 tables

Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following

table name: DownloadTable

fileName |DownloadSize|

file1 | 45

file2 | 50

file3 | 20

--------------

second table

table name: VideoTable

fileName | VideoSize |

file1 | 40

file2 | 60

file3 | 20

------------

Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:

SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable

However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?

Thanks in advance

Regards,

What is the expected output?|||in my real applicaion, the expected value was 3280. However, i got 37624.|||Based on the values you provided how do you arrive at 3280? Is there any business-forumula?|||

well thats the thing. I dont know what i need to do to arrive at the expected value. And when i try to retrieve the sum of the tables indiidually, it works, and i get the expected output. But when it comes time to add up the values of those 2 tables, i get into trouble.

And what is a business-formula?

Thanks for your replies

|||

hervens:
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following
table name: DownloadTable
fileName |DownloadSize|
file1 | 45
file2 | 50
file3 | 20
--------------
second table
table name: VideoTable
fileName | VideoSize |
file1 | 40
file2 | 60
file3 | 20
------------
Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following:
SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable
However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me?

Is that your exact SQL statement? You've not specified which columns to join on, so your result set contains a row for every possible combination of every column in each table.

Instead, try something like this:

DECLARE @.Total BIGINT

SELECT @.Total = SUM(DownloadSize) FROM DownLoadTable
SELECT @.Total = @.Total + (SELECT SUM(VideoSize) FROM VideoTable)

|||

Hello tmorton, thx for your reply. However, when i pressed "test query" button in the dialog box to try to run it, the following error message poped up in a message box.

"The variable name "@.Total" has already been declared. Variable names must be unique within a query batch or stored procedure"

I also tried modifying the variable name total, but got the same error.

Im really sorry about this, im not much of an sql programmer. Just read a tutorial or 2 about it. Oh, and i copied the code you provided exactly

|||Try This Out......

select t = sum(DownloadSize) + (select sum(VideoSize) from VideoTable) from DownloadSize

I think this should work for u.|||Wow, thank a lot Dhaliwal. Everything is working perfectly right now.

Saturday, February 25, 2012

adding leading zeros in select

Hi all,

I'm trying to select a bigint field and format it with leading zeros. I've tried the convert function but it does not seem to support this basic feature. There is no reference in the document on CONVERT or CAST, it only refers to formatting dates.

example:
table contents
123
456
789

desired select result:
000123
000456
000789

Who has a solution for this problem?select
replicate('0', 6-datalength(convert(varchar,NumericField)))
from MyTable

Or, better:

create function LZero(@.MyNum int, @.MyLength int)
returns varchar
as
begin
declare @.MyStr varchar
set @.MyStr = convert(varchar,@.MyNum)
if @.MyLength>datalength(@.MyStr)
set @.MyStr = replicate('0',(@.MyLength-datalength(@.MyStr)))+@.MyStr
return @.MyStr
end
go
select dbo.LZero(NumericField,6) from MyTable|||Thanx for your reply.

The first solution you offer works fine and suits my needs. I can't get the second to work properly however. The query only returns '0' for every row.

But this will get me where I want. Thank you very much|||Ok.
I have no SQL Server near me to debug it, so go for the 1st option.|||Originally posted by kukuk
Ok.
I have no SQL Server near me to debug it, so go for the 1st option.

OK, I will, thanx again|||the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.|||Originally posted by jora
the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.

Thanx for your reply, I already found it out and fixed it.

Thursday, February 16, 2012

Adding database name at runtime

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

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
> > >> >> >
> > >> >
> > >

Monday, March 19, 2012

adding sql values from 2 tables

Saturday, February 25, 2012

adding leading zeros in select

Thursday, February 16, 2012

Adding database name at runtime

Adding database name at runtime

Adding database name at runtime

Sql Activity log backup

Blog Archive

About Me