Sqlserver export table structure to word

-- SQL Server 2000
Select
Table name = case when a. colorder = 1 then D. Name else ''end,
Table description = case when a. colorder = 1 then isnull (F. Value, '') else'' end,
FIELD No. = A. colorder,
Field name = A. Name,
Id = case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then' √ 'else' end,
Primary Key = case when exists (select 1 from sysobjects where xtype = 'pk' and parent_obj = A. ID and name in (
Select name from sysindexes where indid in (
Select indid from sysindexkeys where id = A. ID and colid = A. colid) then' √ 'else' end,
Type = B. Name,
Bytes occupied = A. length,
Length = columnproperty (A. ID, A. Name, 'precision '),
Decimal places = isnull (columnproperty (A. ID, A. Name, 'Scale'), 0 ),
Allow null = case when a. isnullable = 1 then '√ 'else' 'end,
Default Value = isnull (E. Text ,''),
Field description = isnull (G. [value], '')
From
Syscolumns
Left join
Policypes B
On
A. xusertype = B. xusertype
Inner join
Sysobjects d
On
A. ID = D. id and D. xtype = 'U' and D. Name <> 'dtproperties'
Left join
Syscomments E
On
A. cdefault = E. ID
Left join
Sysproperties g
On
A. ID = G. ID and A. colid = G. smallid
Left join
Sysproperties F
On
D. id = f. ID and F. smallid = 0
Where
D. Name = 'table to be query' -- if only the specified table is queried, add this condition.
Order
A. ID, A. colorder

-- SQL Server 2005
-- 1. query table structure information
-- ===================================================== ======================================
-- Query table structure information
-- Producer build 2005.08 (reference please keep this information)
-- ===================================================== ======================================
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

-- 2. Index and primary key information
-- ===================================================== ======================================
-- Index and primary key information
-- Producer build 2005.08 (reference please keep this information)
-- ===================================================== ======================================
Select
Tableid = O. [object_id],
Tablename = O. Name,
Indexid = isnull (KC. [object_id], idx. index_id ),
Indexname = idx. Name,
Indextype = isnull (KC. type_desc, 'index '),
Index_column_id = idxc. index_column_id,
Columnid = C. column_id,
Columnname = C. Name,
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,
[Uqique] = case when idx. is_unique = 1 then n' √ 'else n' end,
Ignore_dup_key = case when idx. ignore_dup_key = 1 then n' √ 'else n'' end,
Disabled = case when idx. is_disabled = 1 then n' √ 'else n'' end,
Fill_factor = idx. fill_factor,
Padded = case when idx. is_padded = 1 then n' √ 'else n'' end
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 SYS. Objects o
On O. [object_id] = idx. [object_id]
Inner join SYS. Columns C
On O. [object_id] = C. [object_id]
And O. type = 'U'
And O. is_ms_shipped = 0
And idxc. column_id = C. column_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