Example to get column details from a SQL-Server database table

readme.md

In SSMS, Azure Data Studio, VS-Code or Visual Studio add the statement, change @TableName to a desire table and ensure the database is selected.

Now imagine a developer with decent SQL skills was tasked with writing this statement, it might take an hour or more or even fail. Karen ask AI to generate the statement which took less than five seconds. So the point is, consider using GitHub Copilot or another AI assistant to assist with writing any SQL and before using statements generated with AI in code test it with for SQL-Server SSMS (SQL-Server Management Studio).

Karen uses this to iterate all databases for a SQL-Server. Below is part of the code.

public static async Task<string> IterateDatabases()
{
	StringBuilder builder = new();
	List<IGrouping<string, DataContainer>> grouped = await DataOperations.ReadDataContainersGroupedAsync();

	await FileOperations.WriteToFileAsync(grouped);

	foreach (var groupItem in grouped.Where(groupItem => !Exclude.DatabaseNameList.Contains(groupItem.Key)))
	{
		builder.AppendLine($"{groupItem.Key}");
		foreach (var item in groupItem)
		{
			if (Exclude.TableNameList.Contains(item.TableName))
			{
				continue;
			}
			builder.AppendLine($"     {item.SchemaName}.{item.TableName}");
			var columns = await DataOperations.ReadColumnDetailsForTableAsync(Utilities.ServerName(), groupItem.Key, item.TableName);
			foreach (var column in columns)
			{
				builder.AppendLine($"          {column.IsPrimaryKey.Primary(),-5}{column.Position,-5}{column.ColumnName,-30}{column.DataTypeFull,-15}{column.IsComputed.ToYesNo()}");
			}
		}
	}

	return builder.ToString();

}

Sample.sql

DECLARE @TableName AS NVARCHAR(MAX) = 'Birthdays'
SELECT 
    X.COLUMN_NAME AS ColumnName, 
    X.ORDINAL_POSITION AS Position, 
    X.TABLE_CATALOG AS Catalog, 
    X.TABLE_SCHEMA AS TableSchema,
    X.COLUMN_DEFAULT AS ColumnDefault, 
    X.DATA_TYPE AS DataType,
    CASE 
        WHEN KCU.COLUMN_NAME IS NOT NULL THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
    END AS IsPrimaryKey,
    CAST(
        X.DATA_TYPE + 
        CASE 
            WHEN X.CHARACTER_MAXIMUM_LENGTH IS NOT NULL AND X.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar') 
                THEN '(' + 
                     CASE 
                         WHEN X.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' 
                         ELSE CAST(X.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR)
                     END + ')'
            WHEN X.DATA_TYPE IN ('decimal', 'numeric')
                THEN '(' + CAST(X.NUMERIC_PRECISION AS NVARCHAR) + ',' + CAST(X.NUMERIC_SCALE AS NVARCHAR) + ')'
            ELSE ''
        END
    AS NVARCHAR(MAX)) AS DataTypeFull,
    CAST(COLUMNPROPERTY(OBJECT_ID(X.TABLE_SCHEMA + '.' + X.TABLE_NAME), X.COLUMN_NAME, 'IsComputed') AS BIT) AS IsComputed
FROM INFORMATION_SCHEMA.COLUMNS AS X
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
    ON X.TABLE_NAME = KCU.TABLE_NAME
    AND X.TABLE_SCHEMA = KCU.TABLE_SCHEMA
    AND X.COLUMN_NAME = KCU.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
    ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
    AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE X.TABLE_NAME = @TableName