Add Additional Partition to an Existing Partition Table

-- Add additional file group. ALTER DATABASE Test02 ADD FILEGROUP [fg202001_tbTable] GO -- Assoicate file to file group. ALTER DATABASE Test02 ADD FILE (NAME = N'fg202001_tbTable', FILENAME = N'D:\MSSQL\DATA\Test02\fg202001_tbTable.ndf' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP [fg202001_tbTable] GO -- Add additional file group to partition scheme. ALTER PARTITION SCHEME... » read more

Creating Partitions on a Table

Create File Group and Associate File to File Group ALTER DATABASE Test02 ADD FILEGROUP [fg201701_tbImageFpMatchARCH] GO ALTER DATABASE Test02 ADD FILE (NAME = N'fg201701_tbTable', FILENAME = N'D:\MSSQL\DATA\Test02\fg201701_tbTable.ndf' , SIZE = 1000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP [fg201701_tbTable] GO ALTER DATABASE Test02 ADD FILEGROUP [fg201702_tbTable] GO ALTER DATABASE Test02 ADD FILE (NAME... » read more

Checking for Partitions

The following query returns one or more rows if the table PartitionTable is partitioned. If the table is not partitioned, no rows are returned. SELECT * FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] IN (0,1) JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id WHERE t.name = 'PartitionTable'; GO The following... » read more

Get All Indexes for a Table

Get all Index for a table… EXEC sys.sp_helpindex @objname = N'tblXXXXX' Or query from the indexes system table… SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ind.* FROM sys.indexes ind INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE t.name = 'tblXXXXX' For those tables with multiple partitions… SELECT o.name objectname,i.name indexname, partition_id,... » read more

List of Child Objects

SELECT name, SCHEMA_NAME(schema_id) AS schema_name, type_desc, type FROM sys.objects WHERE parent_object_id = (OBJECT_ID('dbo.tblXXXXX')) ORDER BY name; Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF... » read more

Database Table Definition with Extended Property

Extended properties can be all sorts of annotations added about an object. They can be added manually, or by a tool. The extended properties has no effect on queries accessing the object. For specifying extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. Level 0 is... » read more

Query for all child objects

SELECT name, SCHEMA_NAME(schema_id) AS schema_name, type_desc , type FROM sys.objects WHERE parent_object_id = (OBJECT_ID(‘dbo.tblXXXX’)) AND type IN (‘C’,’F’,’PK’,’D’, ‘UQ’) order by name; GO