Note: Source Network
-- ========================================================== ==============================
-- Lists All SQL Server tables, field names, primary keys, types, lengths, decimal places, and other information.
-- Run in the query analyzer.
-- ========================================================== ==============================
Select
( Case
When A. colorder = 1 Then D. Name
Else ''
End ) As ' Table Name ' ,
A. colorder As ' FIELD No. ' ,
A. Name As ' Field name ' ,
( Case
When Columnproperty (A. ID, A. Name, ' Isidentity ' ) = 1 Then ' √ '
Else ''
End ) As ' Identifier ' ,
( 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 ) As ' Primary Key ' ,
B. Name As ' Type ' ,
A. Length As ' Bytes occupied ' ,
Columnproperty (A. ID, A. Name, ' Precision ' ) As ' Length ' ,
Isnull ( Columnproperty (A. ID, A. Name, ' Scale ' ), 0 ) As ' Decimal places ' ,
( Case
When A. isnullable = 1 Then ' √ '
Else ''
End ) As ' Null allowed ' ,
Isnull (E. Text , '' ) As ' Default Value ' ,
Isnull (G. value, '' ) As ' Field description '
From Syscolumns
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 A. ID, A. colorder