Identify Column Data Types

--The following offers a result set for just the Database, Schema, Table Name, Column Name, Data Type, and Maximum Character Length from information_schema.columns.
select TABLE_CATALOG AS 'Database', TABLE_SCHEMA AS 'Schema', TABLE_NAME AS 'Table Name', COLUMN_NAME AS 'Column Name', DATA_TYPE AS 'Data Type', CHARACTER_MAXIMUM_LENGTH AS 'Max Length'
from information_schema.columns

--Will provide a count of 'text type' data type columns.
select COUNT (DATA_TYPE)
from information_schema.columns
WHERE DATA_TYPE = 'nvarchar'
OR DATA_TYPE = 'varchar'
OR DATA_TYPE = 'nchar'
OR DATA_TYPE = 'char'
OR DATA_TYPE = 'text'
OR DATA_TYPE = 'ntext'

--Full query, and just a good place to start.
select COUNT (*)
from information_schema.columns