Table Ordinal Position - SQL Server Forums

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)

Table Ordinal Position

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-05-29 : 09:28:52

Hi There

I know i can get that ordinal position of columns within a table using something like...


SELECT		
	c.*
FROM		
	INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN	
	INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
	AND 
		t.TABLE_SCHEMA = c.TABLE_SCHEMA
	AND 
		t.TABLE_NAME = c.TABLE_NAME
	AND 
		t.TABLE_TYPE = 'BASE TABLE'

Is it possible to get the ordinal position of tables within a database?

Can't see anything obvious in the information_schema views but i'm no expert.

Reason: I want to be able to present the table list in a dashboard in the same order as in the management studio

Any ideas?

====
Paul

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-29 : 09:32:27

Aren't they just in alphabetical order?

No, you're never too old to Yak'n'Roll if you're too young to die.

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-05-29 : 09:43:32

Yes sort of

They are in owner.Tablename order e.g. dbo.Mytable etc

Ah i can use the table_schema column in conjunction with the table name

great thanks

====
Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:09:10

whats the need of this requirement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-05-29 : 12:24:26

I'm prseenting the database schema in a BI dashboard for the people i work with so they can better understand what each field in the database does and subsequently help them understand their SQL queries better.

I have done it now with the aid of this query and anything else i needed to do i handled in the BI code and a few mapping tables.


SELECT		
	c.*
	,cu.CONSTRAINT_NAME
FROM		
	INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN	
	INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_CATALOG = c.TABLE_CATALOG
	AND 
		t.TABLE_SCHEMA = c.TABLE_SCHEMA
	AND 
		t.TABLE_NAME = c.TABLE_NAME
	AND 
		t.TABLE_TYPE = 'BASE TABLE'
LEFT JOIN
	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS cu ON cu.TABLE_CATALOG = c.TABLE_CATALOG
	AND 
		cu.TABLE_SCHEMA = c.TABLE_SCHEMA
	AND 
		cu.TABLE_NAME = c.TABLE_NAME
	AND
		cu.COLUMN_NAME = c.COLUMN_NAME
ORDER BY
	c.TABLE_SCHEMA,
	c.TABLE_NAME,
	c.ORDINAL_POSITION

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources