Pivot

Used to convert rows to columns. Example 1: Example 2: Sources:

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

Object Creation File Group Partition Function Partition Schema Partition 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... » read more

Checking for Partitions

Return all partition table in the database. 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... » 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