How to find all tables and its definition in current sql database

Below query will show you all tables definition with column details.

SELECT
SysObjects.[Name] as TableName,
SysColumns.[Name] as ColumnName,
SysTypes.[Name] As DataType,
SysColumns.[Length] As Length
FROM SysObjects
INNER JOIN SysColumns ON SysObjects.[Id] = SysColumns.[Id]
INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE
SysObjects.[type] = 'U'
ORDER BY SysObjects.[Name]


SysObjects.[Type] value's details

1: C = CHECK constraint
2: D = Default or DEFAULT constraint
3: F = FOREIGN KEY constraint
4: FN = Scalar function
5: IF = Inlined table-function
6: K = PRIMARY KEY or UNIQUE constraint
7: L = Log
8: P = Stored procedure
9: R = Rule
10: RF = Replication filter stored procedure
11: S = System table
12: TF = Table function
13: TR = Trigger
14: U = User table
15: V = View
16: X = Extended stored procedure