How to view the SQL Server table index creation time and modification time. Or modify the record.

Select
Table name = case when C. column_id = 1 then O. Name else n'' end,
Table description = isnull (case when C. column_id = 1 then PTB. [value] end, n ''),
FIELD No. = C. column_id,
Field name = C. Name,
Primary Key = isnull (idx. primarykey, n ''),
Id = case when C. is_identity = 1 then n' √ 'else n'' end,
Calculation column = case when C. is_computed = 1 then n' √ 'else n'' end,
Type = T. Name,
Length = C. max_length,
Precision = C. precision,
Decimal places = C. Scale,
Allow null = case when C. is_nullable = 1 then n' √ 'else n'' end,
Default Value = isnull (D. Definition, n ''),
Field description = isnull (PFD. [value], n ''),
Index name = isnull (idx. indexname, n ''),
Index sorting = isnull (idx. Sort, n ''),
Creation Time = O. create_date,
Modification time = O. modify_date
From SYS. Columns C
Inner join SYS. Objects o
On C. [object_id] = O. [object_id]
And O. type = 'U'
And O. is_ms_shipped = 0
Inner join SYS. types t
On C. user_type_id = T. user_type_id
Left join SYS. default_constraints d
On C. [object_id] = D. parent_object_id
And C. column_id = D. parent_column_id
And C. default_object_id = D. [object_id]
Left join SYS. extended_properties PFD
On PFD. Class = 1
And C. [object_id] = PFD. major_id
And C. column_id = PFD. minor_id
-- And PFD. Name = 'caption '-- the description name corresponding to the field description (multiple descriptions of different names can be added for one field)
Left join SYS. extended_properties PTB
On PTB. Class = 1
And PTB. minor_id = 0
And C. [object_id] = PTB. major_id
-- And PFD. Name = 'caption '-- Name of the description corresponding to the table description (multiple descriptions with different names can be added to a table)
Left join -- index and primary key information
(
Select
Idxc. [object_id],
Idxc. column_id,
Sort = case indexkey_property (idxc. [object_id], idxc. index_id, idxc. index_column_id, 'isdesending ')
When 1 then 'desc' when 0 then 'asc 'else' end,
Primarykey = case when idx. is_primary_key = 1 then n' √ 'else n'' end,
Indexname = idx. Name
From SYS. Indexes idx
Inner join SYS. index_columns idxc
On idx. [object_id] = idxc. [object_id]
And idx. index_id = idxc. index_id
Left join SYS. key_constraints KC
On idx. [object_id] = KC. [parent_object_id]
And idx. index_id = KC. unique_index_id
Inner join -- if a column contains multiple indexes, only 1st indexes are displayed.
(
Select [object_id], column_id, index_id = min (index_id)
From SYS. index_columns
Group by [object_id], column_id
) Idxcuq
On idxc. [object_id] = idxcuq. [object_id]
And idxc. column_id = idxcuq. column_id
And idxc. index_id = idxcuq. index_id
) Idx
On C. [object_id] = idx. [object_id]
And C. column_id = idx. column_id
-- Where o. Name = 'viewvoutpage'
-- If only the specified table is queried, add this condition.
Order by O. Name, C. column_id

 

http://topic.csdn.net/u/20090429/10/50c54300-a5f9-422e-9e94-41cb5047f03c.html