How to get all MSSQL database columns names, data types and length

Some times we need to know all database columns names and data types and length for any activity, without this script you must check each database table for its columns names, data types and length but with this simple script you will able to get all these data with the minimum effort:

SELECT SysObjects.[Name] as TableName,

SysColumns.[Name] as ColumnName,

SysTypes.[Name] AS [Name],

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'

AND SysTypes.[Name] <> 'sysname'

ORDER BY SysObjects.[Name]