SQL to get partition key column name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL to get partition key column name

Author

Topic

kitepwr
Starting Member

2 Posts

Posted - 2009-05-15 : 18:40:09

Does anyone know the SQL to retrieve the partition key column name for a given table [for SQL Server 2005]. I've been through all the sys.* views and can't find where this bit of information is buried.

For example, if I create a table like this...
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money)
ON [Data Partition Scheme] (ID);

What I need is an SQL query that would return 'ID' for table MyTable. I.e. "select ??? from ??? where table_name = 'MyTable'.

Thanks in advance,
Darrin

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-15 : 22:36:04

Not sure if I'm following this.

You can return all the system column information using something like


select b.* from 
sys.tables a
INNER JOIN
Sys.columns b
on a.Object_ID = b.Object_ID
where a.name = 'MyTable'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-15 : 22:53:26

try this too
select distinct object_id(table_name) as tableid,* from information_schema.columns where table_name = 'urtablename'

kitepwr
Starting Member

2 Posts

Posted - 2009-05-17 : 15:25:25

Thanks guys for the attempts... I've figured out how to get what I was after. The trick is the partition_ordinal column in sys.index_columns view. The complete SQL is ...


select c.name 
from  sys.tables          t
join  sys.indexes         i 
      on(i.object_id = t.object_id 
      and i.index_id < 2)
join  sys.index_columns  ic 
      on(ic.partition_ordinal > 0 
      and ic.index_id = i.index_id and ic.object_id = t.object_id)
join  sys.columns         c 
      on(c.object_id = ic.object_id 
      and c.column_id = ic.column_id)
where t.object_id  = object_id('<MyTableName>')

Thanks,
Darrin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources