Saturday, May 5, 2007

TSQL : List tables, Indexes & Columns

List tables, Indexes & Columns via system objects -
select sys.objects.object_id,
sys.objects.name as tablename,
sys.indexes.name as indexname,
sys.columns.name as columnname
from sys.objects
inner join sys.indexes 
on sys.objects.object_id = sys.indexes.object_id
inner join sys.index_columns 
on sys.index_columns.object_id = sys.indexes.object_id
and sys.index_columns.index_id = sys.indexes.index_id
inner join sys.columns
on sys.columns.object_id = sys.index_columns.object_id
and sys.columns.column_id = sys.index_columns.column_id
where sys.objects.type_desc  = 'USER_TABLE'
and sys.indexes.is_primary_key = 0

No comments: