By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
5 rows affected
ColumnName | NonNullCount | Datatype | PrimaryKey |
---|---|---|---|
ID | 3 | int | 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 | bigint | No |
WITH Counts AS (
SELECT @Schema AS SchemaName,
@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 + ISNULL(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.SchemaName = ISC.TABLE_SCHEMA
AND 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,
',' + CASE WHEN ISC.DATA_TYPE NOT LIKE '%int' THEN CONVERT(varchar(4),ISC.NUMERIC_PRECISION) END,
',' + CASE WHEN ISC.DATA_TYPE NOT LIKE '%int' THEN CONVERT(varchar(4),ISC.NUMERIC_SCALE) END,
',' + 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_SCHEMA = KCU.TABLE_SCHEMA
AND 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_SCHEMA = ISC.TABLE_SCHEMA
AND TC.TABLE_NAME = ISC.TABLE_NAME) PK;
Warning: Null value is eliminated by an aggregate or other SET operation.