Query to Get database Size;

I was trying to list the size of all databases in a particular server. This query gave me that information.


SELECT SUM(size*8/1024) SizeINMB,db_name(database_id) as DbName
FROM (SELECT distinct size,database_id FROM sys.master_files) F1
GROUP BY database_id

I could also use a powershell script to go against bunch of servers for inventory purposes.
Thanks

Scripting Partitioned Tables From object Explorer;

One of our developer mentioned while scripting INDIVIDUAL partitioned tables from the database the Partition Scheme wasn't included in his script. For example, I created a database and a table on partiton scheme named TestPS. The Partiton Function is TestPF. But When I script that table from object explorer it doesn't include the partition scheme unless we change the scripting option.

But Once I change that option, under Tools-Options->SQL Server ObjectExplorer->Scripting . On the Right under Table and View options-> We need to set Partition Schemes to True So the script includes the partiton Scheme the table was created on.


 CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC)
)ON [TestPS]([CurrentTime]) ON [TestPS]([CurrentTime])
GO


This is the Partition Function and Partition Scheme I used to create the table.
Thanks

USE [TEST]
GO/
****** Object: PartitionFunction [TestPF] Script Date: 02/02/2011 09:17:08 ******/
CREATE PARTITION FUNCTION [TestPF](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-31T23:59:59.997', N'2011-02-28T23:59:59.997')
GO
USE [TEST]
GO
/****** Object: PartitionScheme [TestPS] Script Date: 02/02/2011 09:16:59 ******/
CREATE PARTITION SCHEME [TestPS] AS PARTITION [TestPF] TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO

/****** Object: Table [dbo].[TestPart] Script Date: 02/02/2011 09:28:26 ******/
CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC))

/****** Object: Table [dbo].[TestPart] Script Date: 02/02/2011 09:16:08 ******/
 
CREATE TABLE [dbo].[TestPart]([CurrentTime] [datetime] NOT NULL,[Id] [int] NULL,PRIMARY KEY CLUSTERED ([CurrentTime] ASC))

Query to find partitioned tables;

--One of our development teams were asking about all tables that were partitioned . The two queries at the bottom gave us that information. I am creating a database with two tables one partitioned and one NON partitioned for testing.


(CREATE DATABASE TEST ON PRIMARY(name='test',filename='d:\test.mdf'),name='test_Data1',filename='d:\test_Data.ndf')LOG ON (name='test_Log',filename='c:\test_Log.ldf')


go


USE TEST


GO

CREATE Partition Function [TestPF](datetime) AS RANGE RIGHT FOR VALUES('1/31/2011 23:59:59.997','2/28/2011 23:59:59.997')


GO

CREATE Partition SCHEME TestPS AS PARTITION TestPF TO ([PRIMARY],[PRIMARY],[PRIMARY])


GO

--Create a Partitioned Table on Partition Scheme TestPS


CREATE TABLE TestPart(CurrentTime datetime,Id Int Primary Key Clustered (CurrentTime))on TestPS(CurrentTime)


GO

--Creating an Aligned Index to the same partitioned scheme

CREATE Index NCIX_ID on TestPart(ID) on TestPS(CurrentTime)


GO

--Create a NON Partitioned Table


CREATE TABLE TESTNonPart (UniqueID Int,FirstName Varchar(100) Primary Key Clustered(UniqueID))


GO


--IF you want clustered index Please use this


SELECT  T.NAME,S.NAME,I.NAME
FROM SYS.DATA_SPACES DS  
INNER JOIN SYS.INDEXES i 
ON DS.DATA_SPACE_ID=I.DATA_SPACE_ID 
INNER JOIN SYS.TABLES T
 ON I.OBJECT_ID=T.OBJECT_ID
 INNER JOIN SYS.SCHEMAS S 
 ON S.SCHEMA_ID=T.SCHEMA_ID
WHERE  ds.name='TestPS'  AND I.Index_id=1

--IF you want clustered index and all other indexes Please remove Index_id=1)


SELECT T.NAME,S.NAME,I.NAME 
FROM SYS.DATA_SPACES DS
 INNER JOIN  SYS.INDEXES i
ON DS.DATA_SPACE_ID=I.DATA_SPACE_ID 
INNER JOIN SYS.TABLES T
 ON I.OBJECT_ID=T.OBJECT_ID 
INNER JOIN SYS.SCHEMAS S 
 ON S.SCHEMA_ID=T.SCHEMA_ID 
WHERE ds.name='TestPS'