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'