April 3, 2015 at 8:29 am
Hi
I need to insert sizes of 'adentureworks' database into 'mydatabase' (table 'size')
-- Taken from sp_spaceused:
SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ' MB')
,'unallocated space' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + ' MB')
FROM (
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
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER 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
) AS partitions
April 3, 2015 at 8:45 am
What is the question here?
Here is your query formatted so we can read it.
-- Taken from sp_spaceused:
SELECT database_name = Db_name()
, database_size = Ltrim(Str((CONVERT(DECIMAL(15, 2), dbsize) + CONVERT(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2)
+ ' MB')
, 'unallocated space' = Ltrim(Str(( CASE WHEN dbsize >= reservedpages THEN (CONVERT(DECIMAL(15, 2), dbsize) - CONVERT(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 ELSE 0 END ), 15, 2) + ' MB')
FROM (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) AS files,
(SELECT reservedpages = Sum(a.total_pages)
, usedpages = Sum(a.used_pages)
, pages = Sum(CASE
WHEN it.internal_type IN ( 202, 204, 211, 212,
213, 214, 215, 216 ) THEN 0
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER 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) AS partitions
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2015 at 9:03 am
Hi Sean,
I want to get results of 'adventureworks' database But I want those results inserted into 'size' table in 'mydatabase'.
I already have created a table 'size' with 3 columns in 'mydatabase'.
April 3, 2015 at 9:07 am
Tac11 (4/3/2015)
Hi Sean,I want to get results of 'adventureworks' database But I want those results inserted into 'size' table in 'mydatabase'.
I already have created a table 'size' with 3 columns in 'mydatabase'.
So just use this as the basis for your insert. Run it on the adventureworks database.
INSERT MyDatabase.dbo.size (database_name, database_size, unallocated_space) --or whatever your column names are
SELECT database_name = Db_name()
, database_size = Ltrim(Str((CONVERT(DECIMAL(15, 2), dbsize) + CONVERT(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2)
+ ' MB')
, 'unallocated space' = Ltrim(Str(( CASE WHEN dbsize >= reservedpages THEN (CONVERT(DECIMAL(15, 2), dbsize) - CONVERT(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 ELSE 0 END ), 15, 2) + ' MB')
FROM (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) AS files,
(SELECT reservedpages = Sum(a.total_pages)
, usedpages = Sum(a.used_pages)
, pages = Sum(CASE
WHEN it.internal_type IN ( 202, 204, 211, 212,
213, 214, 215, 216 ) THEN 0
WHEN a.type <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER 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) AS partitions
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2015 at 9:23 am
Thanks Sean, really appreciate your help. One thing wondering is it possible to create a SP on 'Mydatabase' but get info of 'Adventurework' database? OR run against 'Mydatabase' and get 'AdventureWorks' space resluts?
April 3, 2015 at 9:25 am
Tac11 (4/3/2015)
Thanks Sean, really appreciate your help. One thing wondering is it possible to create a SP on 'Mydatabase' but get info of 'Adventurework' database? OR run against 'Mydatabase' and get 'AdventureWorks' space resluts?
Yes. Anytime you want to reference another database on the same server you just need to reference the other database objects with three part naming [Database].[schema].[ObjectName]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply