SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.RootTable (ID int PRIMARY KEY);

CREATE TABLE dbo.SampleTable (ID int IDENTITY PRIMARY KEY,
                              SomeString varchar(10),
                              BigText nvarchar(MAX),
                              Numerical decimal(10,2),
                              Hex varbinary(12),
                              Floating real,
                              SomeDate datetime2(0),
                              ForeignKey int,
                              CONSTRAINT FK_ID FOREIGN KEY (ForeignKey) REFERENCES dbo.RootTable(ID));
INSERT INTO dbo.RootTable
VALUES (1),(2);

INSERT INTO dbo.SampleTable (SomeString,BigText,Numerical,Hex,Floating,SomeDate,ForeignKey)
VALUES('asas',NULL,NULL,0x17,12374.57,'20190101',NULL),
      ('asas','asdhjkasvdajkvda',27.12,0x17,12374.57,'20190101',1),
      (NULL,'asdhjkasvdajkvda',27.12,0x17,12374.57,'20190101',1);
5 rows affected

DECLARE @SQL nvarchar(MAX)
DECLARE @Table sysname = 'SampleTable';

SET @SQL = N'WITH Counts AS (' + NCHAR(13) + NCHAR(10) + 
           N'    SELECT @Table AS TableName,' +
           STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) + 
                         N'           COUNT(' + QUOTENAME(C.COLUMN_NAME) + N') AS ' + QUOTENAME(COLUMN_NAME)
                  FROM INFORMATION_SCHEMA.COLUMNS C
                  WHERE C.TABLE_NAME = @Table
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,14,N'') + NCHAR(13) + NCHAR(10) + 
           N'    FROM ' + QUOTENAME(@Table) + N')' + NCHAR(13) + NCHAR(10) + 
           N'SELECT V.ColumnName,' + NCHAR(13) + NCHAR(10) + 
           N'       V.NonNullCount,' + NCHAR(13) + NCHAR(10) + 
           N'       ISC.DATA_TYPE + DT.S AS Datatype,' + NCHAR(13) + NCHAR(10) +
           N'       ISNULL(PK.IsPrimaryKey,''No'') AS PrimaryKey' + NCHAR(13) + NCHAR(10) +
           N'FROM Counts C' + NCHAR(13) + NCHAR(10) + 
           N'     CROSS APPLY(VALUES' + STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) + 
                                                      N'                       (N' + QUOTENAME(C.COLUMN_NAME,'''') + N',C.' + QUOTENAME(C.COLUMN_NAME) + N')'
                                               FROM INFORMATION_SCHEMA.COLUMNS C
                                               WHERE C.TABLE_NAME = @Table
                                               FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,26,N'') + N')V(ColumnName,NonNullCount)' + NCHAR(13) + NCHAR(10) +
           N'     JOIN INFORMATION_SCHEMA.COLUMNS ISC ON C.TableName = ISC.TABLE_NAME' + NCHAR(13) + NCHAR(10) +
           N'                                        AND V.ColumnName = ISC.COLUMN_NAME' + NCHAR(13) + NCHAR(10) + 
           N'     CROSS APPLY (VALUES(''('' + STUFF(CONCAT('','' + CASE ISC.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN ''MAX'' ELSE CONVERT(varchar(4),ISC.CHARACTER_MAXIMUM_LENGTH) END,'','' + CONVERT(varchar(4),ISC.NUMERIC_PRECISION),'','' + CONVERT(varchar(4),ISC.NUMERIC_SCALE),'','' + CONVERT(varchar(4),ISC.DATETIME_PRECISION)),1,1,'''') + '')'')) DT(S)' + NCHAR(13) + NCHAR(10) +
           N'     OUTER APPLY(SELECT ''Yes'' AS IsPrimaryKey ' + NCHAR(13) + NCHAR(10) + 
           N'                 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC' + NCHAR(13) + NCHAR(10) + 
           N'                      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME' + NCHAR(13) + NCHAR(10) + 
           N'                                                                  AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME' + NCHAR(13) + NCHAR(10) + 
           N'                 WHERE TC.CONSTRAINT_TYPE = ''PRIMARY KEY''' + NCHAR(13) + NCHAR(10) + 
           N'                   AND KCU.COLUMN_NAME = V.ColumnName' + NCHAR(13) + NCHAR(10) + 
           N'                   AND TC.TABLE_NAME = ISC.TABLE_NAME) PK'

PRINT @SQL; --Might need to use SELECT here
--SELECT @SQL;
EXEC sp_executesql @SQL, N'@Table sysname', @Table = @Table;


ColumnName
NonNullCount
Datatype
PrimaryKey


ID
3
int(10,0)
Yes

SomeString
2
varchar(10)
No

BigText
2
nvarchar(MAX)
No

Numerical
2
decimal(10,2)
No

Hex
3
varbinary(12)
No

Floating
3
real(24)
No

SomeDate
3
datetime2(0)
No

ForeignKey
2
int(10,0)
No


WITH Counts AS (
    SELECT @Table AS TableName,COUNT([ID]) AS [ID],
           COUNT([SomeString]) AS [SomeString],
           COUNT([BigText]) AS [BigText],
           COUNT([Numerical]) AS [Numerical],
           COUNT([Hex]) AS [Hex],
           COUNT([Floating]) AS [Floating],
           COUNT([SomeDate]) AS [SomeDate],
           COUNT([ForeignKey]) AS [ForeignKey]
    FROM [SampleTable])
SELECT V.ColumnName,
       V.NonNullCount,
       ISC.DATA_TYPE + DT.S AS Datatype,
       ISNULL(PK.IsPrimaryKey,'No') AS PrimaryKey
FROM Counts C
     CROSS APPLY(VALUES(N'ID',C.[ID]),
                       (N'SomeString',C.[SomeString]),
                       (N'BigText',C.[BigText]),
                       (N'Numerical',C.[Numerical]),
                       (N'Hex',C.[Hex]),
                       (N'Floating',C.[Floating]),
                       (N'SomeDate',C.[SomeDate]),
                       (N'ForeignKey',C.[ForeignKey]))V(ColumnName,NonNullCount)
     JOIN INFORMATION_SCHEMA.COLUMNS ISC ON C.TableName = ISC.TABLE_NAME
                                        AND V.ColumnName = ISC.COLUMN_NAME
     CROSS APPLY (VALUES('(' + STUFF(CONCAT(',' + CASE ISC.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(varchar(4),ISC.CHARACTER_MAXIMUM_LENGTH) END,',' + CONVERT(varchar(4),ISC.NUMERIC_PRECISION),',' + CONVERT(varchar(4),ISC.NUMERIC_SCALE),',' + CONVERT(varchar(4),ISC.DATETIME_PRECISION)),1,1,'') + ')')) DT(S)
     OUTER APPLY(SELECT 'Yes' AS IsPrimaryKey 
                 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
                                                                  AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
                 WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
                   AND KCU.COLUMN_NAME = V.ColumnName
                   AND TC.TABLE_NAME = ISC.TABLE_NAME) PK
Warning: Null value is eliminated by an aggregate or other SET operation.
ColumnName	NonNullCount	Datatype	PrimaryKey
ID	3	int(10,0)	Yes
SomeString	2	varchar(10)	No
BigText	2	nvarchar(MAX)	No
Numerical	2	decimal(10,2)	No
Hex	3	varbinary(12)	No
Floating	3	real(24)	No
SomeDate	3	datetime2(0)	No
ForeignKey	2	int(10,0)	No