There are numerous occasions on daily bases where we need some basic information of the Tables and Columns used in a Database for many different purpose.

Here is a small script which gives you some basic information of Tables and Columns. There are many more columns in the catalogs which I have used, which you can modify, add, Remove, Customise as per your needs.

SELECT DISTINCT SS.NAME AS [TABLE_SCHEMA],ST.NAME AS TABLE_NAME, SC.[NAME] AS COLUMN_NAME
,SC.COLLATION_NAME,SC.IS_NULLABLE AS COLUMN_NULLABLE,SC.IS_REPLICATED AS COLUMN_REPLICATED
,CAST(STY.NAME AS VARCHAR(20)) +'('+
     CAST(CASE ST.NAME
     WHEN 'NVARCHAR' THEN (SELECT SC.MAX_LENGTH/2)
     ELSE (SELECT SC.MAX_LENGTH)
     END AS VARCHAR(20)) +')' AS [DATA_TYPE],
     SC.IS_IDENTITY,ST.IS_PUBLISHED AS TABLE_PUBLISHED,ST.IS_REPLICATED AS TABLE_REPLICATED
FROM SYS.COLUMNS SC
INNER JOIN SYS.TABLES ST
ON SC.OBJECT_ID = ST.OBJECT_ID
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON SC.NAME = ISC.COLUMN_NAME
INNER JOIN SYS.TYPES AS STY
ON SC.USER_TYPE_ID = STY.USER_TYPE_ID
INNER JOIN SYS.SCHEMAS AS SS
ON SS.SCHEMA_ID = ST.SCHEMA_ID