January 25, 2016 at 9:41 am
Question 1
Running the following just gave me one line of output. I was able to get the name
of the primary key. But what I need is the columns that make up the primary key.
Select * FROM sys.indexes where object_id = object_id('visit_in')
Question 2
You might think why I did not run ---> sp_help 'visit_in'
I did, but I waited for 30 seconds and it is still running.
The table has more than 10 million rows.
January 25, 2016 at 9:51 am
You can use the sys.index_columns view.
Select i.name AS index_name,
c.name AS column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
where i.object_id = object_id('visit_in')
AND i.is_primary_key = 1
If sp_help is not returning results after 30 seconds, you might have some schema blockers that might give you problems.
January 25, 2016 at 9:55 am
try this
SELECTobject_name(i.object_id)AS TableName
, i.nameAS IndexName
, STUFF((SELECT ', ' + QUOTENAME(c2.name) +
CASE ic2.is_descending_key
WHEN 0 THEN ' ASC'
ELSE ' DESC'
END
FROM sys.indexes i2 inner join sys.index_columns ic2
ON i2.object_id = ic2.object_id AND i2.index_id = ic2.index_id
INNER JOIN sys.columns c2 ON ic2.object_id = c2.object_id AND ic2.column_id = c2.column_id
WHERE ic2.object_id = i.object_id and ic2.index_id = i.index_id
ORDER BY ic2.index_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') AS ColumnOrder
FROM sys.indexes i INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE i.index_id = 1 AND o.is_ms_shipped = 0
ORDER BY object_name(i.object_id)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply