SQL Server Get Table structure statement

--sql Server 2005
--1. Table Structure Information Query
-- ========================================================================
--Table Structure information query
--Jiangjian 2005.08 (quote please keep this information)
-- ========================================================================
SELECT
Tablename=case when c.column_id=1 then o.name ELSE N ' END,
Tabledesc=isnull (case is 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 and 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 and 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 (a field can add multiple descriptions of different names)
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 '--table description corresponding to the description name (a table can add multiple different name descriptions)
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, ' isdescending ')
When 1 Then ' DESC ' while 0 Then ' ASC ' ELSE ' END,
Primarykey=case when Idx.is_primary_key=1 and 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--Displays only the 1th index information for a column that contains multiple indexes
(
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 query '--if you only query the specified table, add this condition
ORDER by o.name,c.column_id

SQL Server Gets table structure statements