List columns with type, length, nullability and default value

Observed sign or problem

We found the table we were looking for, but we still don’t know what data it expects. Before writing a INSERT, preparing an integration or changing a column, we need a readable x-ray of its structure.

What we need to distinguish

Precision is the total number of digits in a number, and scale is the number after the decimal separator. Nullability indicates whether a column accepts NULL, an absent or unknown value.

The data type does not tell the whole story. Length, precision and scale, ability to save NULL, automatic generation, and default value also matter. These properties determine what input a column can accept.

Query

DECLARE @Tabla NVARCHAR(517) = N'dbo.Pedido';
DECLARE @IdTabla INT = OBJECT_ID(@Tabla, N'U');

IF @IdTabla IS NULL
    THROW 50000, 'La tabla no existe o no es visible en esta base.', 1;

SELECT
    c.column_id AS Orden,
    c.name AS Columna,
    ty.name AS TipoDato,
    CASE
        WHEN c.max_length = -1 THEN N'MAX'
        WHEN ty.name IN (N'nchar', N'nvarchar')
            THEN CONVERT(NVARCHAR(10), c.max_length / 2)
        ELSE CONVERT(NVARCHAR(10), c.max_length)
    END AS Longitud,
    c.precision AS Precision,
    c.scale AS Escala,
    c.is_nullable AS AceptaNull,
    c.is_identity AS EsIdentity,
    c.is_computed AS EsCalculada,
    dc.definition AS ValorPredeterminado,
    c.collation_name AS Intercalacion
FROM sys.columns AS c
INNER JOIN sys.types AS ty
    ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints AS dc
    ON dc.object_id = c.default_object_id
WHERE c.object_id = @IdTabla
ORDER BY c.column_id;

Parameters or permissions

Change @Tabla to a two-part name. The query works on the current database and requires visibility over the table. Catalog views can ignore objects that the account does not have permissions to.

max_length is saved in bytes; that’s why we divide by two into nchar and nvarchar. In decimal and numeric, check Precision and Escala. User-defined types retain the name registered in sys.types.

How to read the result

IDENTITY automatically generates a number for each row. A computed expression derives a value from other columns, and a default value is used when INSERT omits the column.

Each row corresponds to a column and Orden reproduces its declared position. AceptaNull = 0 indicates that the value is required, unless SQL Server can produce it using IDENTITY, a calculated expression, or a default value.

ValorPredeterminado displays the expression of the constraint, for example ((0)) or (sysdatetime()). The extra parentheses are part of how SQL Server stores the definition; They don’t necessarily change their intention.

A default value is used when the INSERT omits the column or requests DEFAULT. It does not automatically replace an explicitly sent NULL and does not describe values that already exist.

What this does establish

It allows you to describe the visible structure of a table and recognize which columns require input, which generate values, and what basic limits each type imposes.

What this still does not establish

A CHECK constraint is a condition enforced by the table when data is inserted or changed; this query does not display it.

It does not demonstrate which columns an application uses or what additional rules its code applies. It also does not show CHECK restrictions, keys, relationships, data sensitivity or distribution of values.

The definition admits certain data; That does not mean that all existing values are correct from a business perspective.

Next reasonable check

Contrast the structure with foreign keys and keys and indexes. Before changing a type or nullability, measure the existing values and locate the modules that read or modify the column.

Search SQL Server