|
Home > Archive > MS SQL Server > November 2006 > Adding database name at runtime
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Adding database name at runtime
|
|
| ipramod@gmail.com 2006-11-09, 7:13 pm |
| Hi,
I have below SQL query which calculates the database size for all
databases.
select sum(convert(bigint,c
ase when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,c
ase 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
| |
| Uri Dimant 2006-11-09, 7:13 pm |
| Hi
EXEC sp_MSForeachdb 'use [?]; select db_name();select
sum(convert(bigint,c
ase when status & 64 = 0 then size else 0 end)) +
sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0
end)) + sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0
> end)) + sum(convert(bigint,c
ase 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
>
| |
| ipramod@gmail.com 2006-11-09, 7:13 pm |
| 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,c
ase when status & 64 = 0 then size else 0 end)),
@logsize = sum(convert(bigint,c
ase when status & 64 <> 0 then size else
0 end))FROM ?.dbo.sysfiles'
SELECT @dbsize = sum(convert(bigint,c
ase when status & 64 = 0 then
size else 0 end)), @logsize = sum(convert(bigint,c
ase 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=(co
nvert (dec (15,2),@dbsize) - convert
(dec (15,2),@reservedpage
s)) * 8192 / 1048576
---------------------
SET @str = str((@unallocatedsiz
e*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:[color=darkred
]
> Hi
> EXEC sp_MSForeachdb 'use [?]; select db_name();select
> sum(convert(bigint,c
ase when status & 64 = 0 then size else 0 end)) +
> sum(convert(bigint,c
ase 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,c
ase when status & 64 = 0 then size else 0 end)),
> @logsize = sum(convert(bigint,c
ase when status & 64 <> 0 then size else
> 0 end))FROM ?.dbo.sysfiles'
>
> SELECT @dbsize = sum(convert(bigint,c
ase when status & 64 = 0 then
> size else 0 end)), @logsize = sum(convert(bigint,c
ase 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=(co
nvert (dec (15,2),@dbsize) - convert
> (dec (15,2),@reservedpage
s)) * 8192 / 1048576
> ---------------------
>
> SET @str = str((@unallocatedsiz
e*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:
>
| |
| ipramod@gmail.com 2006-11-09, 7:13 pm |
| SQL Server 2005 RTM Version
Thanks,
Regards,
Pramod
vt wrote:[color=darkred
]
> 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:
>
| |
| ipramod@gmail.com 2006-11-10, 12:12 am |
| Hi Vt,
I have tried the same with SQL Server 2000 also, but it is not working.
Regards,
Pramod
vt wrote:[color=darkred
]
> Sorry buddy.. still using 2000
>
>
>
> <ipramod@gmail.com> wrote in message
> news:1163082808.701906.266480@f16g2000cwb.googlegroups.com...
| |
| ipramod@gmail.com 2006-11-10, 5:14 am |
| 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,c
ase when status & 64 = 0 then
size else 0 end)), @logsize1 = sum(convert(bigint,c
ase 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=(co
nvert (dec (15,2),@dbsize)*1.00 -
convert (dec (15,2),@reservedpage
s)*1.00) * 8192.00 / 1048576.00
SET @str =
str((@unallocatedsiz
e*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:[color=darkred
]
> Hi Vt,
>
> I have tried the same with SQL Server 2000 also, but it is not working.
>
> Regards,
> Pramod
>
> vt wrote:
|
|
|
|
|