Obtain all the table names in the database and the table structure (column name and data type) in MSSQL)

This article from: http://kyle.itpub.net/post/1626/8891

The original article is as follows:
------------------ Obtain the names of all tables in the database, and the fields and types of fields corresponding to the table, which are stored in the # Magic temporary table
If exists (select * From tempdb .. sysobjects where name like '# Magic % ')
Drop table # Magic
Go
Select a. Name, B. Name Col, C. Name type
Into # Magic
From sysobjects A, syscolumns B, policypes C
Where a. xtype = 'U'
And a. ID = B. ID
And B. xtype = C. xtype
And B. xusertype = C. xusertype

-------------------- Process the result
Select (case when tmp3.no = 1 then tmp3.name else ''end) Name, Col, Type
From
(Select top 100000000 name
, (Select count (1) from # Magic tmp1
Where tmp1.name = tmp2.name
And tmp1.col <= tmp2.col) as no, Col, Type
From # Magic tmp2
Order by name, Col, type)
Tmp3

 

-------------------- The following method is switched from csdn
Select
(Case when a. colorder = 1 then D. Name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. Name n' field name ',
(Case when columnproperty (A. ID, A. Name, 'isidentity ') = 1 then' √ 'else' 'end) n' ',
(Case when (select count (*)
From sysobjects
Where (name in
(Select name
From sysindexes
Where (ID = A. ID) and (indid in
(Select indid
From sysindexkeys
Where (ID = A. ID) and (colid in
(Select colid
From syscolumns
Where (ID = A. ID) and (name = A. Name) and
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. Name n' type ',
A. length N 'number of bytes occupied ',
Columnproperty (A. ID, A. Name, 'precision ') as N 'length ',
Isnull (columnproperty (A. ID, A. Name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (E. Text, '') n' default value ',
Isnull (G. [value], '') as n' field description'
-- Into # TX

From syscolumns a left join policypes B
On a. xtype = 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
Order by object_name (A. ID), A. colorder

------------------ Create another view
Select TBL. Name as tablename, TBL. xtype as tabletype, col. Name as fieldname,
TT. Name as fieldtype, col. length as fieldlength
From DBO. syscolumns Col inner join
DBO. sysobjects TBL on col. ID = TBL. ID inner join
DBO. policypes tt on col. xtype = TT. xtype
Where (TBL. xtype = 'U') and (TT. Name <> N 'sysname ')