October 26, 2011 at 6:28 am
Simpler if you change the datatype in the temp table ;-).
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] DECIMAL(18,2)
, [Available Space in MB] DECIMAL(18,2)
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space in MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128.0) AS ''TotalSizeMB''
, SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS ''AvailableSpaceMB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
Select DBNAME, [Total Size in MB], [Available Space in MB]
,PercentFree = CONVERT(DECIMAL(18,2), [Available Space in MB] / [Total Size in MB] * 100)
from #dbs
drop table #dbs
October 26, 2011 at 6:31 am
derekr 43208 (10/26/2011)
Cadavre (10/26/2011)
How's this?
DECLARE @sql AS VARCHAR(MAX)SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
EXEC(@SQL)
Cadavre
Unfortuanately I need to be able to put it into a Temp table
I tried but it said that I cannot insert from a select statement that has a variable
Something like that
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] DECIMAL(18,2)
, [Available Space in MB] DECIMAL(18,2)
, DriveLetter CHAR(1)
)
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space in MB]
, DriveLetter
)
EXEC(@SQL)
SELECT * FROM #dbs
drop table #dbs
October 26, 2011 at 6:38 am
Ninja's_RGR'us (10/26/2011)
derekr 43208 (10/26/2011)
Cadavre (10/26/2011)
How's this?
DECLARE @sql AS VARCHAR(MAX)SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
EXEC(@SQL)
Cadavre
Unfortuanately I need to be able to put it into a Temp table
I tried but it said that I cannot insert from a select statement that has a variable
Something like that
CREATE TABLE #dbs(
DBNAME sysname
, DBID INT
, [Total Size in MB] DECIMAL(18,2)
, [Available Space in MB] DECIMAL(18,2)
, DriveLetter CHAR(1)
)
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space in MB]
, DriveLetter
)
EXEC(@SQL)
SELECT * FROM #dbs
drop table #dbs
Great stuff
Thanks Ninja
Appreciate it.....
October 26, 2011 at 6:39 am
And Thanks Cadavre
October 26, 2011 at 7:04 am
derekr 43208 (10/26/2011)
CadavreUnfortuanately I need to be able to put it into a Temp table
I tried but it said that I cannot insert from a select statement that has a variable
Something like that
Sorry, was in a meeting so didn't catch your post.
Ninja's_RGR'us (10/26/2011)
CREATE TABLE #dbs(
DBNAME sysname
, DBID INT
, [Total Size in MB] DECIMAL(18,2)
, [Available Space in MB] DECIMAL(18,2)
, DriveLetter CHAR(1)
)
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT DB_NAME() As DBNAME,
DB_ID() AS DBID, SUM(size / 128.0) AS TotalSizeMB,
SUM(size / 128.0 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128.0) AS AvailableSpaceMB,
LEFT(physical_name, 1) AS DriveLetter
FROM sys.database_files
WHERE type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1)' AS sql_command
FROM sys.databases
) a
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space in MB]
, DriveLetter
)
EXEC(@SQL)
SELECT * FROM #dbs
drop table #dbs
Thanks for covering 🙂
October 26, 2011 at 7:11 am
NP.
Was what so important in that meeting that could make this unbeknownst guy wait for his answer! 😀
You should talk to the authorities about that!
October 26, 2011 at 7:15 am
Ninja's_RGR'us (10/26/2011)
NP.Was what so important in that meeting that could make this unbeknownst guy wait for his answer! 😀
You should talk to the authorities about that!
heh. I'll attend by WebEx next time, promise 😛
October 26, 2011 at 7:17 am
Cadavre (10/26/2011)
Ninja's_RGR'us (10/26/2011)
NP.Was what so important in that meeting that could make this unbeknownst guy wait for his answer! 😀
You should talk to the authorities about that!
heh. I'll attend by WebEx next time, promise 😛
You migh include that in the "the good job" thread :w00t:.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply