Query sqlserver database Schema

SELECT
TableName = case when c. column_id = 1 then o. name ELSE n'' END,
TableDesc = ISNULL (case when c. column_id = 1 then ptb. [value] END, n ''),
Column_id = C. column_id,
ColumnName = C. name,
PrimaryKey = ISNULL (IDX. PrimaryKey, n ''),
[IDENTITY] = case when c. is_identity = 1 THEN n' √ 'else n' END,
Computed = case when c. is_computed = 1 THEN n' √ 'else n'' END,
Type = T. name,
Length = C. max_length,
Precision = C. precision,
Scale = C. scale,
NullAble = case when c. is_nullable = 1 THEN n' √ 'else n'' END,
[Default] = ISNULL (D. definition, n ''),
ColumnDesc = ISNULL (PFD. [value], n ''),
IndexName = ISNULL (IDX. IndexName, n ''),
IndexSort = ISNULL (IDX. Sort, n ''),
Create_Date = O. Create_Date,
Modify_Date = 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 = n' table to be queried '-- this condition is added if only the specified table is queried.
Order by o. name, C. column_id