Query to get PrimaryKey, ForeignKey, IdentityKey, UniqueKey column names

DECLARE  @TableName NVARCHAR(50)
SET @TableName = 'PkFkIkTestIdentAsPk'
 
 SELECT
  C.COLUMN_NAME,
  C.DATA_TYPE,
  C.ORDINAL_POSITION 
 FROM
  INFORMATION_SCHEMA.COLUMNS C 
  LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS COL
  ON col.TABLE_NAME = C.TABLE_NAME 
  AND col.COLUMN_NAME = C.COLUMN_NAME
  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblCons 
 ON tblCons.CONSTRAINT_NAME = col.CONSTRAINT_NAME 
 WHERE tblCons.TABLE_NAME = @TableName 
  AND 
  (
   tblCons.CONSTRAINT_TYPE = 'PRIMARY KEY' 
  OR tblCons.CONSTRAINT_TYPE = 'FOREIGN KEY' 
  OR tblCons.CONSTRAINT_TYPE = 'UNIQUE' 
  )
 UNION 
  SELECT C.COLUMN_NAME,
  C.DATA_TYPE,
  C.ORDINAL_POSITION
  FROM
  INFORMATION_SCHEMA.COLUMNS C
  WHERE C.TABLE_NAME = @TableName
  AND COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME), C.COLUMN_NAME,'IsIdentity') = 1

SQL SERVER DataTypes

To get the DataType information
SP_HELP DataTypeName

SELECT 'User_type' = name, 
       'Storage_type' = Type_name(xtype), 
       'Length(Size)' = length, 
       'Prec' = Typeproperty(name, 'precision'), 
       'Scale' = Typeproperty(name, 'scale'), 
       'Nullable' = CASE 
                      WHEN Typeproperty(name, 'AllowsNull') = 1 THEN 'Yes' 
                      ELSE 'No' 
                    END, 
       'Default_name' = Isnull(Object_name(tdefault), 'None'), 
       'Rule_name' = Isnull(Object_name(domain), 'None'), 
       'Collation' = collation 
FROM   systypes 
ORDER  BY name  

2008	2005	2000
Exact Numerics	Exact Numerics	Exact Numerics
bigint	bigint	bigint
numeric	numeric	numeric
bit	bit	bit
smallint	smallint	smallint
decimal	decimal	decimal
smallmoney	smallmoney	smallmoney
int	int	int
tinyint	tinyint	tinyint
money	money	money
Approximate Numerics	Approximate Numerics	Approximate Numerics
float	float	float
real	real	real
Date and Time	Date and Time	Date and Time
date
datetimeoffset
datetime2
smalldatetime	smalldatetime	smalldatetime
datetime	datetime	datetime
time
Character Strings	Character Strings	Character Strings
char	char	char
varchar	varchar	varchar
text	text	text
Unicode Character Strings	Unicode Character Strings	Unicode Character Strings
nchar	nchar	nchar
nvarchar	nvarchar	nvarchar
ntext	ntext	ntext
Binary Strings	Binary Strings	Binary Strings
binary	binary	binary
varbinary	varbinary	varbinary
image	image	image
Other Data Types	Other Data Types	Other Data Types
cursor	cursor	cursor
timestamp	timestamp	timestamp
hierarchyid
uniqueidentifier	uniqueidentifier	uniqueidentifier
sql_variant	sql_variant	sql_variant
xml	xml
table	table	table
2008	2005	2000