請問如何用TSQL語法查詢Partition Scheme使用的Column?

Question

各位前輩

請問如何用TSQL語法查詢Partition Scheme使用的Column

就是在屬性中Storage的Partition Column (如下圖)

感激不盡

Answer 1

Try this:

select

OBJECT_NAME(i.object_id), c.name from sys.index_columns i join sys.columns c on i.object_id = c.object_id

and

i.column_id = c.column_id where partition_ordinal = 1

Answer 2

hi

參考看看

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.columns AS c
    ON t.object_id = c.object_id
JOIN sys.partition_schemes AS ps
    ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
WHERE t.name = 'your partition name'
AND i.type <= 1
AND c.column_id = 1;

參考

http://www.dotblogs.com.tw/ricochen/archive/2012/05/04/71971.aspx

---

• Convert C# to VB.NET
• Welcom to my personal blog

保證解答-微軟技術支援服務

Answer 3

非常感謝 rmiao 與 ricoisme 兩位的協助，謝謝